Concat numerous fields in script

[#1]

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)

00

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

[swuehl · Accepted solution]

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;

[#3]

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

Thanks,

Ralph