topic Re: Help in Backend calculation in QlikView

Help in Backend calculation

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help in Backend calculation

lironbaram — Fri, 12 Oct 2018 05:31:31 GMT

have a look at the attach example

Re: Help in Backend calculation

tresB — Fri, 12 Oct 2018 05:50:39 GMT

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

];

Re: Help in Backend calculation

mahitham — Fri, 12 Oct 2018 06:02:05 GMT

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.

Re: Help in Backend calculation

lironbaram — Fri, 12 Oct 2018 06:15:44 GMT

attach is an example in Qlik sense

in the future post your question in Qlik sense forums

Re: Help in Backend calculation

mahitham — Sat, 13 Oct 2018 06:39:27 GMT

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.