Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

How to Create Cross Table

Hi 

I have Table like this:

CategoryDateValue
A01/12/2019100
B01/12/2019200
C02/12/2019300
D03/12/2019524
A06/12/2019547
B07/12/2019784
C09/12/2019547
D08/12/2019887

 

I need to create like this:

DateABCD
01/12/201910020000
02/12/2019003000
03/12/2019000300
06/12/2019547000
07/12/2019078400
08/12/2019005470
09/12/2019000887
1 Solution

Accepted Solutions
lironbaram
Partner
Partner

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;

View solution in original post

5 Replies
lironbaram
Partner
Partner

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
Creator III
Creator III
Author

Hi

Thanks for your reply

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

lironbaram
Partner
Partner

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;

View solution in original post

Lucke_Hallbergson

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

//Lucke

Shahzad_Ahsan
Creator III
Creator III
Author

Hi Lironbaram

Thanks this is working fine