topic Re: Script Help in QlikView

Script Help

Tue, 19 Jan 2016 18:11:04 GMT

Hi All,

Greetings for the day!

I have one table which contains Region, Country, Year,Amount1,Amoun2,Amount3,Amount4,Amount5,FirstTotal,SecTotal.

Here FirstTotal = Amount1 + Amount2 + Amount3

SecTotal = Amount4 + Amount5

Actually I have to convert all Amount columns into one column "Amount".

For that i used Crosstable load as below

CrossTable (ConcField,Amount,3)

here ConField contains values like Amount1,Amoun2,Amount3,Amount4,Amount5,FirstTotal,SecTotal and Amount field stores all the amount values.

But as per req i have to create 2 dimensions using ConcField column as below:

Dimen1 = if ConcField = Amount1,Amoun2,Amount3 then ConcField

Dimen2 = if ConcField = Amoun4,Amount5 then ConcField

I tried it but didn't get exactly. Could you please help me here.

I have attached sample QVW with expected result.

Thanks,

M V

Re: Script Help

sunny_talwar — Tue, 19 Jan 2016 18:24:45 GMT

Try this:

Tab1:

CrossTable (ConcField,Amount,3)

LOAD Region,

Country,

Year,

FirstTotal&'|'&Amount1,

FirstTotal&'|'&Amount2,

FirstTotal&'|'&Amount3,

SecTotal&'|'&Amount4,

SecTotal&'|'&Amount5

FROM

[SData.xlsx]

(ooxml, embedded labels, table is Sheet1);

Tab2:

LOAD Region,

Country,

Year,

PurgeChar(SubField(ConcField, '|', 1), '&'&Chr(39)) as Dimen1,

PurgeChar(SubField(ConcField, '|', 2), '&'&Chr(39)) as Dimen2,

SubField(Amount, '|', 2) as Amount

Resident Tab1;

DROP Table Tab1;

Re: Script Help

swuehl — Tue, 19 Jan 2016 18:29:29 GMT

Maybe like this:

Tab2:
LOAD Region, 
    Country, 
    Year,
    ConcField,
    if(Match(ConcField,'Amount1','Amount2','Amount3'),'FirstTotal','SecTotal') as Dimen1,
    // if(ConcField = 'Amount1' or ConcField ='Amount2' or ConcField = 'Amount3' or ConcField ='Amount4'  or ConcField ='Amount5',ConcField) as Dimen2,
    Amount
    Resident Tab1
    WHERE not ConcField Like '*Total';
    
DROP Table Tab1;

Re: Script Help

maxgro — Tue, 19 Jan 2016 18:30:36 GMT

maybe

Tab2:

LOAD Region,

Country,

Year,

ConcField,

if(Match(ConcField,'Amount1','Amount2','Amount3'),'First', 'Second') as Dimen1,

if(Match(ConcField,'Amount1','Amount2','Amount3','Amount4','Amount5'),ConcField) as Dimen2,

Amount

Resident Tab1;