Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Convert Columns to Rows in load script

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Convert Columns to Rows in load script

For i = 2 to NoOfFields('SourceTable')

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

  Final:

  LOAD

  ID,

  '$(field)' as FlagName,

  $(field) as Value

  resident SourceTable;

next

5 Replies
Employee
Employee

Re: Convert Columns to Rows in load script

For i = 2 to NoOfFields('SourceTable')

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

  Final:

  LOAD

  ID,

  '$(field)' as FlagName,

  $(field) as Value

  resident SourceTable;

next

sasikanth
Valued Contributor III

Re: Convert Columns to Rows in load script

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

Re: Re: Convert Columns to Rows in load script

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

Employee
Employee

Re: Re: Re: Convert Columns to Rows in load script

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

Re: Re: Convert Columns to Rows in load script

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

Community Browser