topic Re: pass the field name into expression using variable in QlikView

pass the field name into expression using variable

jeevays7 — Fri, 14 Jul 2017 11:40:06 GMT

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

Re: pass the field name into expression using variable

MK_QSL — Fri, 14 Jul 2017 11:48:56 GMT

Use below script.. I think rest of the things easy from here.. You can use Value * CurrencyValue

CrossTable(Base_Curr,Cur)
TempT2:
Load * Inline
[
Date, EUR, USD
1, 20, 10
2, 10, 50
3, 30, 20
];


T2:
Mapping Load Date & Base_Curr as Key, Cur Resident TempT2;


Drop Table TempT2;


T1:
Load 
*,
ApplyMap('T2',Date & Base_Curr) as CurrencyValue
Inline
[
Date, NAME, Base_Curr, Value
1, A, USD, 10
2, B, EUR, 10
3, C, USD, 20
];

Re: pass the field name into expression using variable

kaushiknsolanki — Fri, 14 Jul 2017 11:51:56 GMT

Hi,

Try below code.

Table1:

LOad *,Date&Base_Curr as Key inline [

Date,NAME,Base_Curr,Value

1,A,USD,10

2,B,EUR,10

3,C,USD,20

];

Table2:

CrossTable(Curr,CurrVal)

Load * inline [

Date,EUR,USD

1,20,10

2,10,50

3,30,20

];

Left join(Table1)

Load Date&Curr as Key,CurrVal

Resident Table2;

Drop table Table2;

NOw create a chart with dimension like Date,Base_Curr,Name

And expression as Sum(Value*CurrVal)

Regards,

Kaushik Solanki

Re: pass the field name into expression using variable

jeevays7 — Fri, 14 Jul 2017 12:05:14 GMT

Hi Manish,

I don't need keys. because i have one level before name. so

Branch Name

A A

A B

A is USD and B is EUR,

so when we select A in branch, Name's all values should multiple with USD only,

if we select a name(i.e A) in Name field, that particular currency value should be multiplied.

How it is working?

Re: pass the field name into expression using variable

MK_QSL — Fri, 14 Jul 2017 12:17:26 GMT

1) I don't understand what you have explained

2) You can add one more line to my previous code.

Drop Field Key;

Re: pass the field name into expression using variable

jeevays7 — Fri, 14 Jul 2017 12:39:27 GMT

Thank you Manish,

Re: pass the field name into expression using variable

jeevays7 — Fri, 14 Jul 2017 14:24:11 GMT

Can we pass field name by using variable?

Re: pass the field name into expression using variable

jeevays7 — Fri, 14 Jul 2017 14:25:48 GMT

Manish,

Can we do the same thing without Date&currency key??

Re: pass the field name into expression using variable

Anonymous — Fri, 14 Jul 2017 16:04:27 GMT

Try:

SET vCurrency='if(Base_Curr='EUR'['EUR],[USD])';

Or on the frontend just enter with no equal sign, I think.

Then in your expression use a $ sign expansion like

=SUM(Value*$(vCurrency))

Re: pass the field name into expression using variable

effinty2112 — Fri, 14 Jul 2017 20:49:48 GMT

Hi Jeremy,

Try:

Rates:

Load * Inline [

Date, EUR, USD

1, 20, 10

2, 10, 50

3, 30, 20

];

Values:

Load * Inline [

Date, NAME, Base_Curr, Value

1, A, USD, 10

2, B, EUR, 10

3, C, USD, 20

];

Date	NAME	Base_Curr	*Sum(ValuePick(Match(Base_Curr,'USD','EUR'), USD,EUR))**
			600
1	A	USD	100
2	B	EUR	100
3	C	USD	400

Cheers

Andrew

Re: pass the field name into expression using variable

MK_QSL — Sat, 15 Jul 2017 07:54:01 GMT

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;

Re: pass the field name into expression using variable

jeevays7 — Mon, 17 Jul 2017 10:00:53 GMT

Thank you andrew

Re: pass the field name into expression using variable

jeevays7 — Mon, 17 Jul 2017 10:02:05 GMT

Thank you for your replyWallo