Convert Columns to Rows in load script

[Anonymous]

Hi

I have data in a resident table and below is a some sample data, plus the bb.qvw attached has the sample data in it

This loads sample data into SourceTable.  In reality there will be a few dozen Flag fields and a few million ID's.

SourceTable:

LOAD * INLINE [

    ID, FlagA, FlagB, FlagC

    1, Yes, Yes, Yes

    2, Yes, Yes, No

    3, Yes, No, Yes

    4, No, Yes, No

    5, No, No, Yes

    6, No, No, No

];

This Inline Load shows what I wish this sample data to be transformed into:

WantedTable :

LOAD * INLINE [

    ID, FlagName, Value

    1, FlagA, Yes

    1, FlagB, Yes

    1, FlagC, Yes

    2, FlagA, Yes

    2, FlagB, Yes

    2, FlagC, No

    3, FlagA, Yes

    3, FlagB, No

    3, FlagC, Yes

    4, FlagA, No

    4, FlagB, Yes

    4, FlagC, No

    5, FlagA, No

    5, FlagB, No

    5, FlagC, Yes

    6, FlagA, No

    6, FlagB, No

    6, FlagC, No

];

I have gone snowblind bananas trying to work out  how to do this, any suggestions would be most welcome.

Best Regards,     Bill

[Clever_Anjos · Accepted solution]

For i = 2 to NoOfFields('SourceTable')
  let field = FieldName(i,'SourceTable');
  Final:
  LOAD
  ID,
  '$(field)' as FlagName,
  $(field) as Value
  resident SourceTable;
next

[sasikanth]

Hi Bill

Here we have to use CrossTable to convert l

CrossTable(Flagname,value,1)

LOAD * INLINE [

    ID, FlagA, FlagB, FlagC

    1, Yes, Yes, Yes

    2, Yes, Yes, No

    3, Yes, No, Yes

    4, No, Yes, No

    5, No, No, Yes

    6, No, No, No

];

You will get u r desired result

Regards

sasi

[Anonymous]

Anjos

Many thanks that has cracked it !!

....except that it produce a separate table for each Flag.

So I have added the concatenate and pre-created the table with a Dummy field.

Final:

LOAD * INLINE [

    Dummy

    Dummy

];

For i = 2 to NoOfFields('SourceTable')

  let field = FieldName(i,'SourceTable');

  Concatenate (  Final  )

  LOAD

  ID,

  '$(field)' as FlagName,

  $(field) as Value

  resident SourceTable;

next

drop field Dummy;

This works perfectly now, but it seems a little inelegant to have to pre-create the dummy table.

Any suggestions for elegance ?

Best Regards,     Bill

[Clever_Anjos]

Weird... not happened here

But this will fix:

LET v='Final:';
For i = 2 to NoOfFields('SourceTable')
  let field = FieldName(i,'SourceTable');
  $(v)
  LOAD
  ID,
  '$(field)' as FlagName,
  $(field) as Value
  resident SourceTable;
  if i > 2 then
  let v='Concatenate(Final)';
  end if;
next

[Anonymous]

Sasi

Your suggestion also cracks it.

I change it to test it from resident and it handles that fine.

SourceTable:

LOAD * INLINE [

    ID, FlagA, FlagB, FlagC

    1, Yes, Yes, Yes

    2, Yes, Yes, No

    3, Yes, No, Yes

    4, No, Yes, No

    5, No, No, Yes

    6, No, No, No

];

OutputTable:

CrossTable(Flagname,value,1)

LOAD *

resident SourceTable

;

And is more elegant.

I'll test both suggestions tomorrow with loads of data..............

Best Regards,     Bill