Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can anyone please help me with below.
I want to store all the values from a filed into Variables so i can later use it in where clause as match function.
example:
FiledA
Apple,
banana,
mango
i want to load a variable like vFruits = ('Apple','banana','mango')
so, i can use the variable vFruits in other tables as match function.
Load *
where match 'v$(vFruits)'
Thanks in Advance
Hello,
You can use For each statement with fieldvaluelist :
It will be something like :
TMP_TABLE:
FiledA
Apple,
banana,
mango
From <TABLE>
For each vFruits in fieldvaluelist('FieldA')
Load *
From <Table B>
Where fruits = $(vFruits);
Next
Regards,
If you want to filter a Load using values previously loaded, a better option than Match is "Exists".
LOAD *
Where Exists(FieldA)
If you do have a need to build a list in a variable as you asked, one way to do that is:
Data:
LOAD * Inline [
FieldA
Apple
Banana
Mango
]
;
TempVar:
LOAD '(''' & Concat(FieldValue('FieldA', RecNo()), ''',''') & ''')' as FruitList
AutoGenerate FieldValueCount('FieldA');
Let vFruits = Peek('FruitList');
Drop Table TempVar;
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com