Script Help

[#1]

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

[sunny_talwar · Accepted solution]

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;   

[swuehl]

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;   

[maxgro]

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;