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

Convert Columns to Rows in load script

Anonymous — Thu, 31 Oct 2013 18:49:10 GMT

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

Re: Convert Columns to Rows in load script

Clever_Anjos — Thu, 31 Oct 2013 18:54:09 GMT

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

Re: Convert Columns to Rows in load script

sasikanth — Thu, 31 Oct 2013 18:58:39 GMT

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

Anonymous — Thu, 31 Oct 2013 19:25:04 GMT

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

];

For i = 2 to NoOfFields('SourceTable')

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

Concatenate ( Final )

LOAD

ID,

'$(field)' as FlagName,

$(field) as Value

resident SourceTable;

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

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

Clever_Anjos — Thu, 31 Oct 2013 19:38:21 GMT

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

Anonymous — Thu, 31 Oct 2013 19:38:28 GMT

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