Calculate different logic based on predefined logic in a field

[Carol_Wu_]

Hi Expert,

I have a question that want to seek an answer.

I have a field D in which I saved different logics based on field A, field B or field C.

Then I need to calculate D in the scripts, because my real data have many many different logics.

So the output from the scripts should be: 

May I know if you have any ideas on how to realize it, thanks!

[Anil_Babu_Samineni]

What is the logic behind for D column from row level?

[Saravanan_Desingh]

Try this,

tab1:
LOAD RowNo() As ID,* INLINE [
    A, B, C, Exp_D
    1, 2, 0.95, A*B*C
    1000, 20, 1, A*C
    1, 100, 2, B*C
];

Expression 😧 Pick(ID,A*B*C,A*C,B*C)

[Saravanan_Desingh]

Ouput:

[Carol_Wu_]

Hi Saran,

This method is ok for this file, but for my real data, there are around 100K row with around hundred logics in different rows. 

So i think i can't write pick function for it. 

[Carol_Wu_]

The D column is defined by user to calculate the rate value based on different conditions.

So I have hundred logics in different rows of data like below. I have the field sales_selling_price, total_gold_value and org_labour_cost. So i need to based on the expression to calculate the logic out. 

[Carol_Wu_]

I have solved my question already by using the loop function. thanks

[Saravanan_Desingh]

Try this. If you think its faster than your code, you can use it.

tab1:
LOAD RowNo() As ID,* INLINE [
    A, B, C, Exp_D
    1, 2, 0.95, A*B*C
    1000, 20, 1, A*C
    1, 100, 2, B*C
];

tab2:
LOAD Concat(Exp_D,',',ID) As StrPick
Resident tab1;

Let vPick=Peek('StrPick');

tab3:
LOAD ID, A, B, C, Pick(ID,$(vPick)) As D
Resident tab1;

Drop Table tab1, tab2;