topic Concat numerous fields in script in QlikView

Concat numerous fields in script

Fri, 11 Oct 2013 10:08:15 GMT

Hello,

I have a table which has 8 fields with Boolean values and am wanting to concat the positive values into one field at the script level (see below example)

IDField	Movement	Decline	Forward	Submit	Etc	Etc2	Etc3	Etc4
1	0	0	0	1	1	0
2	1	1	1	1	0	0	0	1
3	1	0	0	1	1	1	0	0

Obviously if there were 2 or 3 Boolean value fields I could do an if statement in the script but because there are 8 there would be too many combinations. Does anyone have a straightforward suggestions as to how I could combine all positive values into one field? Using IDField1 as an example the new field would be 'Etc1; Etc2; Etc3'

Thanks,

Ralph

Re: Concat numerous fields in script

swuehl — Fri, 11 Oct 2013 10:17:07 GMT

I think your sample table in the post got mixed up, please check.

If I understand correctly, you can try transforming your table from a cross table to a straight table

TMP:

CROSSTABLE (Field, Value) LOAD IDField, Movement, Decline, Etc RESIDENT YourTable;

This creates a table with 3 fields, IDField, Field, Value

Then Load this Table to get your result like

RESULT:

LOAD IDField, Concat(Field,';') as ConcatField RESIDENT TMP WHERE Value >0 GROUP BY IDField;

Re: Concat numerous fields in script

Fri, 11 Oct 2013 11:01:47 GMT

Perfect that works, didn't even cross my mind to use a Cross Table.

Thanks,

Ralph