topic Re: building a var from a load ... in QlikView

building a var from a load ...

Wed, 02 Jan 2013 11:31:37 GMT

i have a resident table :

code, value

A 1000

B 2000

C 3000

I wish to build up a var containing a list of t5he codes eg. A,B,C

any ides ???

Re: building a var from a load ...

Gysbert_Wassenaar — Wed, 02 Jan 2013 11:38:06 GMT

concat(code,',') will create a comma separated list of values from the field code. Create a variable (i.e. vCodeList) and use the expression concat(code,',') as value. You can then use the variable in expressions: $(vCodeList). If your field code has duplicates you can use the distinct keyword to make a list of unique codes: concat(distinct code,',').

If you want to do this in the script you can do it with something like this:

T2:

load concat(code,',') as CodeList

Resident T1; // <-- change T1 in the name of your resident table that has the field code

LET vCodeList = peek('CodeList');

DROP TABLE T2;

Re: building a var from a load ...

Anonymous — Wed, 02 Jan 2013 11:48:17 GMT

The following may help:

SET a = 0;

SET CodeList = '';

DO WHILE a <= 200 // Test 200 rows as a limit

LET NextCode = Peek('Code', a,'TheTable');

EXIT DO UNLESS Len(NextCode)>0 // Check code is populated

LET CodeList = if(Len(CodeList)=0,NextCode,CodeList&','&NextCode); // Start new list or add to existing

LET a = a + 1;

LOOP // WHILE a <= 200

Setting th 200 in the loop is just in case the code doesn't work. Rmove if you're more confident than I am. Ideally you'd want to get a count of the Codes in TheTable using FieldIndex() but I couldn't get that to work when I was originallly implementing this.

Jonathan

Re: building a var from a load ...

Wed, 02 Jan 2013 13:16:27 GMT

thanks Gysbert ...

... where are you based ?

Re: building a var from a load ...

Wed, 02 Jan 2013 16:22:32 GMT

great stuff ...

thnxs jon, which sector doyou work in ???

Re: building a var from a load ...

Anonymous — Thu, 03 Jan 2013 08:56:14 GMT

Won't bore everyone on the forum and blur the knowledge in the threads, but feel free to email me offline.

Jonathan