Redesign excel data

[agni_gold]

Hi Friends,

I need one help to redesign excel source data , attached in output for (sheet name : output in attached excel)

I have tried , cross table , generic load these option but not able to create , any one can help here.

[micheledenardi]

CrossTable(Period,Values,3)

BaseTab_tmp:

LOAD A,

     B,

     C,

     [2016 - Sales],

     [2017 - Sales],

     [2016 - Budget],

     [2017 - Budget]

FROM

[Sample Data.xlsx] (ooxml, embedded labels, table is Source);

BaseTab:

NoConcatenate

Load *,

  left(Period,4) as Year,

  mid(Period,index(Period,'-')+2,len(Period))as Type

resident BaseTab_tmp;

[agni_gold]

type should be different columns, like sales and budget

[micheledenardi]

See attached qvw

[agni_gold]

if u see my attched excel , output require Sales and budget as column

[Peter_Cammaert]

Two possible solutions:

• serialize the data and put them in a Pivot table - this is the simplest technique, script-wise
• go-on and create straight table data in your script using a LOAD and a JOIN

See attachment.

BTW It would be better to load data from the same source as the Excel instead of trying to twist the Excel aggregation back into something that looked like a source table and then doing the exact same thing again in QlikView. Would save you a lot of headaches in the future...

[agni_gold]

Thanks , but client data is specified in source format , i need this output format in script level , as per requirement from client .

[Peter_Cammaert]

Don't bother. If you run the script to the end, you'll get your required internal table. See sheet 'Complex' for a straight table showing the target data set. If you want to get that one back into Excel, export it using the XLS icon at the right of the caption or open the menu.

[micheledenardi]

See attached qvw

[agni_gold]

Sorry Peter , i just saw chart , not script.

Thanks for your great help.