cancel
Showing results for 
Search instead for 
Did you mean: 

Scripting help.

Former Member
0 Kudos

Hi Team,

I have one requirement where i have 2 tables :

Cabinet master Table :

CabinetOutletCompany Code
C1O1

CC1

C2O2CC2
C3O3CC3

and Cabinet Transaction table : (Input Schema )

CabinetOutletCompanyCodecal day
C1O5CC520140101
C2O2CC220140102
C3O3CC720140103

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

CaldayCabinetOutletMatchIDCorrect Company codeCorrect Outlet
20140101C1O5NCC1O1
20140102C2O2Y
20140103C3O3NCC3O3

Pls help me how to achieve this.

Regards,

Praveen.

Accepted Solutions (1)

Accepted Solutions (1)

former_member187605
Active Contributor
0 Kudos

Use lookup_ext built-in function (right-click in Schema Out in your Query transform and select New Fuction Call.. > Lookup Functions > lookup_ext).

  • Specify 1 conditions: Cabinet = Cabinet.
  • Specify 2 output columns: Company Code --> Correct Company Code and Outlet --> Correct Outlet.

Add a 2nd Query transform and map:

  • Correct Company Code to decode(Company Code = Correct Company Code and Outlet = Correct Outlet, null, Correct Company Code)
  • Correct Outlet to decode(Company Code = Correct Company Code and Outlet = Correct Outlet, null, Correct Outlet)
  • MatchId to decode(Company Code = Correct Company Code and Outlet = Correct Outlet, 'Y', 'N')
Former Member
0 Kudos

Hi Dirk,

Thanks will try and let u know.

Regards,

Praveen.

Former Member
0 Kudos

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.

former_member187605
Active Contributor
0 Kudos

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 .

Former Member
0 Kudos

Hi Dirk,

Thanks for the input,

Can you please elaborate 2nd point.

Regards,

Praveen.

former_member187605
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

Thank you so much Dirk.

Regards,

Praveen.

Answers (0)