Filtering on the Look up fields

Question

Created a conditional lookup based on following :
T1 joined to T2 where T1.A = T2.B
Looked up T2.C based on the above join condition and stored the value in T1.D
Now I need to filter the resultant data set which is on T1 based on certain values of T1.D
Unable to put that filter in the Table Data Selection Rule.
Even I tried to copy T1.D(look up filed) to T1.E (a separate filed created just to hold the value of T1.D) and filter on T1.E, even that did not work.
Appreciate help on the same.

JTreadwell · Accepted Answer

Hi Supriyo,Jan, Thank you for your great description of the process and suggestions. I also have another suggestion in mind.You can create a custom data selection rule. I'll show an example using PurchaseOrderHeader and PurchaseOrderDetail from the Adventureworks database. These two tables encounter the same issue as your situation, where the order date is only located in the header table.So once you have brought both tables into the solutionRight click on PurcahaseOrderDetail > Add Data Selection Rule.Then in the bottom of the Data Selection Pane on the right, be sure the operator is set to Custom, then click Add. This opens up a scripting window.I can then use the following script to filter the PurchaseOrderDetail table based on the OrderDate located in the Header table:[PurchaseOrderID] > =(SELECT MIN(PurchaseOrderID)FROM PurchaseOrderHeaderWHERE OrderDate >='01-01-2014')This method is actually filtering on the PurcahseOrderID (the join of the two tables) based on the OrderDate of my choice.One limitation here is that you would need to ensure the PurchaseOrderHeader table is executed prior to the PurcahseOrderDetail. But if you are doing lookups into Detail then this would always be the case.Once done, you can click OK and Deploy/Execute the table to validate your changes. The final solution should look something like this:Hope that works for you, let me know if you have any questions.Happy Developing!

Anonymous · Answer

Hi SupriyoA data selection rule is working as a filter on the original data. It is a rule that is applied to the transfer process going into the table.Lookups and transformations are applied in the data cleansing process which happen after the transfer. Therefore they are not available fore Table Data Selection Rules.You would normally have several options to limit data after data cleansing:Setup a data validation rule that invalidates the unwanted rows - kind of noisy and not considered best practice.Place the Table Data Selection Rule on the next table that are going to use the data (typical in the MDW layer if you have done the transformation/lookup in stage/business unit)Create a new table with a table insert from T1 with the Selection rule applied - it you need a physical copy of the data in the same database. Perhaps deselect physical valid table on T1.Create a custom view of T1 as SELECT * FROM T1 Where (Row I like).Create a custom script on T1 for post data cleansing execution as DELETE FROM T1_V WHERE (rows I don't like).There is probably more options but this is some of the ones that I have used before to manage the requirement that you are describing.I hope this will help you :)/Jan Bøllingtoft

Filtering on the Look up fields

4 replies

Reply

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded