topic Easy way to transform tablecolumn to headerrow? in QlikView

Easy way to transform tablecolumn to headerrow?

Mon, 16 Jan 2017 11:17:36 GMT

Hi,

i am trying to transform a table column to a headerrow. My data looks like this:

ReportType	RecordNo.	FieldNo.	Content
10	758	3001	NR:11
10	758	3002	VA:1
10	758	3003	VA:5.79
10	758	3005	NR:2
10	758	3007	NR:101

What i am trying to achieve is a format like this:

ReportType

RecordNo.

3001

3002

3003

3005

3007

758

NR:11

VA:1

VA:5.79

NR:2

NR:101

I already tried out the CrossTable() Function, but with no success.

Is this even possible with the crosstable function?
Is there some other function that can help me with this problem?

Any help is really appreciated!

Philipp

Re: Easy way to transform tablecolumn to headerrow?

lironbaram — Mon, 16 Jan 2017 12:52:31 GMT

the function you need is Generic load

which create saperate table for each value in FeildNo. field

and add the values from the field Content

attach is an example and below is a script

your script should look like this

Data:

generic LOAD

RecordNo.,

FieldNo.,

Content

FROM

(ooxml, embedded labels, table is Sheet1);

let vTables= NoofTables(); // finding number of tables

let vDataTableName = TableName(0);////finding the name of first table

RENAME Table $(vDataTableName) to Data;//rename first table

//////////////////////connecting all tables back to one table //////////////////////////

////////////////creating a table with all table names //////////////////////////////////////////

for i =1 to NoofTables()-1

Tables:

load TableName($(i)) as TableName ,$(i) as RN AutoGenerate 1;

set errormode = 0 ;

for i=1 to NoofTables()-1

////////////////////////////loop the tables and joinion them to the Data table ///////////////////////

let vTableName = FieldValue('TableName', $(i));

join (Data)

load * resident $(vTableName) ;

drop table $(vTableName);

drop Table Tables;

/////////////////adding raw data dim attirbutes////////////////

left join (Data)

LOAD distinct ReportType,

RecordNo.

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Easy way to transform tablecolumn to headerrow?

Mon, 16 Jan 2017 13:18:21 GMT

Thanks! Works like a charm.