Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
Hi
Thanks for your reply
This script is giving error "Table ' ' Not found"
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;
Or simply load Your table as is and create a pivottable to present it?
//Lucke
Hi Lironbaram
Thanks this is working fine