Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement.
I have a table like below
Input:
Load * Inline [
Key,Status,Amount1,Amount2
145,Yes,10.2,50
145,No,14.2,40
];
from the above table I need to extract the fields like below. Please help me on this. Is there any way to do in Generic load or any other way please let me know.
Expected output:
Key Status_Yes(Amount1) Status_No(Amount1) Staus_Yes(Amount2) Status_No(Amount2)
145 10.2 14.2 50 40
Thanks in advance
hi
attach is an example in Qlik sense
in the future post your question in Qlik sense forums
hi
have a look at the attach example
Or, may be using a simpler code (Crosstable) and using calculated dimension in pivot table like:
Crosstable(Amount, Value,2)
Load * Inline [
Key,Status,Amount1,Amount2
145,Yes,10.2,50
145,No,14.2,40
];
Hi lironbaram,
Thanks for your reply.
Could you please provide the script here. I don't have the qlikview license. I am working on qliksense.
hi
attach is an example in Qlik sense
in the future post your question in Qlik sense forums
Hi lironbaram,
Thanks a lot for your reply.
In the above attached app getting the expected output but when I have reloaded the app getting the below error.
Could you please help me to avoid this error in the script.
InputTemp:
Load * Inline [
Key,Status,Amount1,Amount2
145,Yes,10.2,50
145,No,14.2,40
];
/////////////converting the table to 3 columns //////////
InputTemp1:
Crosstable (Type, Amount, 2)
load * resident InputTemp;
drop table InputTemp;
///////////////creating a key from status and type and running generic load to convert to one row //////////
Input:
Generic load Key ,
Status& '_' & Type As Status ,
Amount
Resident InputTemp1;
drop table InputTemp1;
//////////////concatenating the tables to one table /////////////////////////////
let vTables= NoofTables(); // finding the number of tables
let vDataTableName = TableName(0);////finding the first table
RENAME Table $(vDataTableName) to Input;//converting the first table name
set errormode = 0;
////////////////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 through all tables//////////////////////
let vTableName = FieldValue('TableName', $(i));
////////////////////////////adding the data////////////////////////////////////
join (Input)
load * resident $(vTableName) ;
drop table $(vTableName);
next
drop Table Tables;
set errormode=1;
Thanks in advance.