building a var from a load ...

[#1]

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 ???

[Gysbert_Wassenaar]

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;

[Anonymous]

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

[#4]

thanks Gysbert ...

... where are you based ?

[#5]

great stuff ...

thnxs jon, which sector doyou work in ???

[Anonymous]

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

Jonathan