pass the field name into expression using variable

[jeevays7]

Hi All,

I have two tables like below

Table 1:

Date	NAME	Base_Curr	Value
1	A	USD	10
2	B	EUR	10
3	C	USD	20

Table 2:

Date	EUR	USD
1	20	10
2	10	50
3	30	20

the above table are linked with Date.

now i need the below Result:

Date	NAME	Base_Curr	Value
1	A	USD	100
2	B	EUR	100
3	C	USD	400

Here im using below expression:

SUM(Value*(vCurrecy))

inside the variable i have the expression

if(Base_Curr='EUR','EUR','USD')

Explanation of above result table:

Date	NAME	Base_Curr	Value	explaination	ex
1	A	USD	100	here base currecy is USD so USD values will come ie.10) from table 2	10*10
2	B	EUR	100	here base currecy is EUR so EUR value will come ie.10) from table 2	10*10
3	C	USD	400	here base currecy is USD so USD values will come ie.20) from table 2	20*20

[MK_QSL]

I don't know why you insist not to do it without Key.

You can do it without using Key but then you have to use Date & Base_Curr.

Le me give you real world scenario.

You might have table like below.

Date, EUR, USD

01/07/2017, 20, 10

10/07/2017, 10, 50

14/07/2017, 30, 20

In short, you don't have currency rates for all days. In fact, to reduce the number of lines, we have stored currency rates when there is actually a change wrt previous day.

How to tackle this type of issue with your question...? You can use below code for that.

CrossTable(Base_Curr,Cur)  
TempT2:  
Load * Inline  
[  
Date, EUR, USD  
01/07/2017, 20, 10  
10/07/2017, 10, 50  
14/07/2017, 30, 20  
]; 


Temp_Currency:
Load 
Date as FromDate,
Date(IF(Base_Curr = Previous(Base_Curr), Previous(Date)-1,Date(Date+1))) as ToDate,
Base_Curr,
Cur
Resident TempT2
Order By Base_Curr, Date Desc;


Drop Table TempT2;


T1:  
Load * Inline  
[  
Date, NAME, Base_Curr, Value  
02/07/2017, A, USD, 10  
12/07/2017, B, EUR, 10  
14/07/2017, C, USD, 20  
];  


Inner Join 
IntervalMatch(Date, Base_Curr)
Load FromDate, ToDate, Base_Curr Resident Temp_Currency;


Left Join(T1)
Load * Resident Temp_Currency;


Drop Table Temp_Currency;


Drop Field FromDate, ToDate;

[jeevays7]

Thank you andrew

[jeevays7]

Thank you for your replyWallo