Easy way to transform tablecolumn to headerrow?

[#1]

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
10	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

[lironbaram · Accepted solution]

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

[#3]

Thanks! Works like a charm.