A challenging logic

[vnspn]

Hi,

We have a tricky requirement to handle as the one mentioned below.

The incoming source link has columns like, -
Id,
Name,
Stat_Cd,
Brok_Cd,
Fin_Cd,
Ln_Cd.

The lookup table has columns (Id, Ele_Nm) with these kinds of data, -

Id -----> Ele_Nm
---------------------
10 -----> Stat_Cd
20 -----> Brok_Cd
30 -----> Ind_Cd
40 -----> Fin_Cd
50 -----> Ln_Cd


Based on the value of the column "Ele_Nm" that is returned by the lookup link, we would need to pass that column
to the output link. The columns on the output link might be, -
Id,
Name,
Code


For example, if, for the first record, the returned lookup value is "Stat_Cd", then we have to pass the column
"Stat_Cd" to the output column "Code". Then if, for the second record, the returned value is "Fin_Cd", then we have to pass the column "Fin_Cd" to the output column "Code".

Its basically dynamically deciding which source column to pass to the output link based on the value returned by
the lookup.

Please give us any ideas or thoughts that you have on handling this kind of scenario.

Thanks.

[ArndW]

This is an interesting problem. I cannot see a truly elegant solution that would cater for n columns, my initial thought is to use a transform stage with IF-THEN-ELSE constructs that explicitly assign the input columns to the output value.

[wannabexpert]

try this one.
let source is source link
target is target link
lookup is lookup link
mapping is as follows.
pass source.id to target.id and key for lookup
pass source.name to target.name
pass lookup.ele.num to target.code
have i decoded the scenario correctly?

[Minhajuddin]

Yeah as AndrW says, your best bet would be to use an If Else Logic in the derivation of a transformer.
The derivation of the column Code would be.

Code --> If(Ele_Nm='Stat_Cd') then Inp.Stat_Cd else
if.................................


Hope that helps.

[vnspn]

Wannabexpert,

You ask to pass the lookup.ele_nm to the target.code.
Lets say, the value returned in lookup.ele_nm column is "Brok_Cd", then we do not want to pass the value "Brok_Cd" into the output column; but we need to pass the value in the column Brok_Cd itself into the output column. Thats the requirement we have.

try this one.
let source is source link
target is target link
lookup is lookup link
mapping is as follows.
pass source.id to target.id and key for lookup
pass source.name to target.name
pass lookup.ele.num to target.code
have i decoded the scenario correctly?

[vnspn]

Minhajuddin,

Yeah, this was the same idea that we too had. But what we were thinking was to avoid explicitly checking the lookup value and passing that source column to the output.

We were thinking of trying to pass the input column dynamically using some kind of expressions and/or any available BASIC utilities, instead of passing the required column by hard-coding it.

[mdan]

1. pivot the table

id,col1,col2,col3,col4
10, a, b, c, d

=>
id, coltype, value
10, col1, a
10, col2, b
10, col3, c
10, col4, d

2. lookup with keys (id, coltype); ignore the results which doesn't match (continue on link condition)

[vnspn]

So, how could I bring the Column name into as, Column value.

That is, as per your example below, how can I take the column names "col1", "col2", "col3", etc into the column "coltype" as values...

Let me know if my question is not clear.

1. pivot the table

id,col1,col2,col3,col4
10, a, b, c, d

=>
id, coltype, value
10, col1, a
10, col2, b
10, col3, c
10, col4, d

2. lookup with keys (id, coltype); ignore the results which doesn't match (continue on link condition)

[mdan]

Just use a column generator and hardcode the column names, before the pivot.
e.g. generate columns cname 1..4, values "col1", "col2", ...
in pivot define coltype as cname1, cname2, ...

br,
dan

So, how could I bring the Column name into as, Column value.

That is, as per your example below, how can I take the column names "col1", "col2", "col3", etc into the column "coltype" as values...

Let me know if my question is not clear.

1. pivot the table

id,col1,col2,col3,col4
10, a, b, c, d

=>
id, coltype, value
10, col1, a
10, col2, b
10, col3, c
10, col4, d

2. lookup with keys (id, coltype); ignore the results which doesn't match (continue on link condition)