on 11-12-2015 6:24 PM
Hi Team,
I have one requirement where i have 2 tables :
Cabinet master Table :
Cabinet | Outlet | Company Code |
---|---|---|
C1 | O1 | CC1 |
C2 | O2 | CC2 |
C3 | O3 | CC3 |
and Cabinet Transaction table : (Input Schema )
Cabinet | Outlet | CompanyCode | cal day |
---|---|---|---|
C1 | O5 | CC5 | 20140101 |
C2 | O2 | CC2 | 20140102 |
C3 | O3 | CC7 | 20140103 |
Now my requirement is :
I have to check whether the Cabinet is placed in the right outlet or not by performing the lookup on th Cabinet master and i need to display the Match ID
where it has to display the value as Y or N, Y means the Cabinet is placed in the correct outlet and N means the Cabinet is placed in the wrong Outlet ( By performing the lookup on the Cabinet Master Table ) . Now finally my outlput should display as
Calday | Cabinet | Outlet | MatchID | Correct Company code | Correct Outlet | |
---|---|---|---|---|---|---|
20140101 | C1 | O5 | N | CC1 | O1 | |
20140102 | C2 | O2 | Y | |||
20140103 | C3 | O3 | N | CC3 | O3 | |
Pls help me how to achieve this.
Regards,
Praveen.
Use lookup_ext built-in function (right-click in Schema Out in your Query transform and select New Fuction Call.. > Lookup Functions > lookup_ext).
Add a 2nd Query transform and map:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Dirk,
Many thanks for the input, it's working.
In the above input, i need some clarfication :
1) What is the difference between Decode and IFTHENELSE Condition.
2)
Use lookup_ext built-in function (right-click in Schema Out in your Query transform and select New Fuction Call.. > Lookup Functions > lookup_ext).
Instead of Rightclick and selecting the New Function Call, We can create New Output column and we can write the lookup in the mapping tab.
Please correct me if i were wrong.
Regards,
Praveen.
1/. There's no difference between decode and ifthenelse in any of these examples. Decode is a syntactically easier representation of ifthenelse with nested conditions only.
2/. You cannot use the lookup_ext function in the mapping of a single output column because in this case it returns 2 output columns. You have to do it my way .
You can only use functions returning a single value in a column mapping.
When a function returns multiple values, like lookup_ext in this example (Company Code and Outlet), you must use New Function Call.. The result will be 2 columns filled in the target schema. You can verify this by checking the input schema of the next transform in your data flow.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.