How to transform multiple fields in one ?

[suzel404]

Hi all,

I have a table like this:

Load

     *,

     FLAG_1,

     FLAG_2,

     FLAG_3,

     FLAG_4

     FLAG_7

     FLAG_8

     FLAG_10

FROM QVD;

I want to transform all FLAG_ in one field FLAG like FLAG contains values : {1,2,3,4,7,8,10}

How can I realize it ?

Thanks for your help

Regards,

[swuehl · Accepted solution]

Maybe you want to look into the CROSSTABLE LOAD prefix:

CROSS:

CROSSTABLE (FlagName, FlagValue)

LOAD 

     Recno() as RecID,

     FLAG_1,

     FLAG_2,

     FLAG_3,

     FLAG_4,

     FLAG_7,

     FLAG_8,

     FLAG_10

   INLINE [

     FLAG_1,     FLAG_2,     FLAG_3,     FLAG_4,     FLAG_7,     FLAG_8,     FLAG_10 

     1,0,1,1,0,0,1

   ];

RESULT:

LOAD RecID, SubField(FlagName,'_',2) as Flag, FlagValue

RESIDENT CROSS;

DROP TABLE CROSS;

The Crosstable Load

[Anonymous]

Hi Joshua,

you can concatenate the Flag values (&) ,  in order to get {1,2,3,4,7,8,10} try this:

Load

     *,

     '{ ' &FLAG_1 &' ,' & FLAG_2 &' ,' & FLAG_3 &' ,' & FLAG_4 &' ,' &   FLAG_7 &' ,' & FLAG_8 &' ,' & FLAG_10& '}'  as Flag

FROM QVD;

You can also change the name of the new field by changing "Flag" after "as"

I hope that helps!

[suzel404]

thanks swuehl !

it was exactly what I need.