Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mahitham
Contributor

Help in Backend calculation

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

1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Help in Backend calculation

hi

attach is an example in Qlik sense

in the future post your question in Qlik sense forums

5 Replies
lironbaram
Honored Contributor II

Re: Help in Backend calculation

hi

have a look at the attach example

MVP
MVP

Re: Help in Backend calculation

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


];

Capture.JPG

mahitham
Contributor

Re: Help in Backend calculation

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.

lironbaram
Honored Contributor II

Re: Help in Backend calculation

hi

attach is an example in Qlik sense

in the future post your question in Qlik sense forums

mahitham
Contributor

Re: Help in Backend calculation

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;

Generic error.png

Thanks in advance.