Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Easy way to transform tablecolumn to headerrow?

Hi,

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

ReportTypeRecordNo.FieldNo.Content
107583001NR:11
107583002VA:1
107583003VA:5.79
107583005NR:2
107583007NR:101

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

ReportType
RecordNo.30013002300330053007
10758NR:11VA:1VA:5.79NR:2NR: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

1 Solution

Accepted Solutions
lironbaram
Partner
Partner

hi

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;

NEXT

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);

next

drop Table Tables;

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

left join (Data)

LOAD distinct ReportType,

    RecordNo.

FROM

(ooxml, embedded labels, table is Sheet1);

View solution in original post

2 Replies
lironbaram
Partner
Partner

hi

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;

NEXT

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);

next

drop Table Tables;

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

left join (Data)

LOAD distinct ReportType,

    RecordNo.

FROM

(ooxml, embedded labels, table is Sheet1);

Not applicable
Author

Thanks! Works like a charm.