How to Create Cross Table

[Shahzad_Ahsan]

Hi 

I have Table like this:

Category	Date	Value
A	01/12/2019	100
B	01/12/2019	200
C	02/12/2019	300
D	03/12/2019	524
A	06/12/2019	547
B	07/12/2019	784
C	09/12/2019	547
D	08/12/2019	887

I need to create like this:

Date	A	B	C	D
01/12/2019	100	200	0	0
02/12/2019	0	0	300	0
03/12/2019	0	0	0	300
06/12/2019	547	0	0	0
07/12/2019	0	784	0	0
08/12/2019	0	0	547	0
09/12/2019	0	0	0	887

[lironbaram]

hi 

using genric load , you'll be able to achieve

here is example of script 

Temp1:
load * Inline [
Category, Date, Value
A, 01/12/2019, 100
B, 01/12/2019, 200
C, 02/12/2019, 300
D, 03/12/2019, 524
A, 06/12/2019, 547
B, 07/12/2019, 784
C, 09/12/2019, 547
D, 08/12/2019, 887
];

Data:
generic load Date,Category,Value
Resident Temp1;

drop Table Temp1;

let vTables= NoofTables(); // find all tables

let vDataTableName = TableName(0);////selecting the first table

RENAME Table $(vDataTableName) to Data;//rename the first table
//////////////////////connecting all the tables from the genric proccess//////////////////////////
////////////////finding all tables //////////////////////////////////////////
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
////////////////////////////looping on all tables //////////////////////
let vTableName = FieldValue('TableName', $(i));

join (Data)
load * resident $(vTableName) ;
drop table $(vTableName);
next

drop Table Tables;

[Shahzad_Ahsan]

Hi

Thanks for your reply

This script is giving error "Table ' '  Not found"

[lironbaram · Accepted solution]

hi 

this one shouldn't give you an error

Temp1:
load * Inline [
Category,	Date,	Value
A,	01/12/2019,	100
B,	01/12/2019,	200
C,	02/12/2019,	300
D,	03/12/2019,	524
A,	06/12/2019,	547
B,	07/12/2019,	784
C,	09/12/2019,	547
D,	08/12/2019,	887
];

Data:
generic load Date,Category,Value
Resident Temp1;

drop Table Temp1;


let vTables= NoofTables()-1; // find all tables

////////////////find all tables that have Data. in thier name ////////////////////////
for i=0 to $(vTables)
      if WildMatch( TableName($(i)),'Data.*') then
     	tables:
        load  TableName($(i)) as tableName
        AutoGenerate 1;
      end if
next


       



let vDataTableName = peek('tableName',0,'tables'); ;////selecting the first table to make it the general table)



RENAME Table $(vDataTableName) to Data;//rename the first table
//////////////////////connecting all the tables from the genric proccess//////////////////////////
////////////////finding all tables //////////////////////////////////////////
for i=1 to NoOfRows('tables')-1
////////////////////////////looping on all tables //////////////////////
let vTableName =   peek('tableName',$(i),'tables');;

join (Data)
load * resident  $(vTableName) ;
drop table $(vTableName);
next

drop Table tables;

[Lucke_Hallbergson]

Or simply load Your table as is and create a pivottable to present it?

//Lucke

[Shahzad_Ahsan]

Hi Lironbaram

Thanks this is working fine