Qlikview Loading.

[abhijith28]

Please find the attached .xls file.

I need the the output as Month field, Brand name, all data as a table.

please help me with the solution.

[prieper · Accepted solution]

Plse check CROSSTABLE-functionality,

[techvarun]

Try the below code

Crosstable ( Month, Sales, 1) LOAD * INLINE [
DNP sales, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
Budget 2015-AGP, 176.62, 164.53, 176.94, 204.58, 207.45, 215.22, 224.01, 220.81, 216.68, 223.52, 232.64, 256.61
Budget 2015-AGA, 20.28, 18.6, 19.5, 17.41, 15.18, 21.13, 16.42, 21.75, 25.96, 22.34, 16.76, 15.34
];

[Vegar]

You could use a loop and a constable(). I'll try to write up a pseudo code below.

For each vBrand in ...
Crosstable (month, DNPSales,2)
Load '$(vBrand)' as brand,
*
From excel.xlsx tab=$(vBrand);
Next

[abhijith28]

But if i use cross table function, i am not able to use this if condition 

CrossTable(month, Data)
LOAD [DNP sales], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total,

if([DNP sales] = 'Spare Parts Budget 2015','Spare', if([DNP sales] ='Spare Parts Budget 2015Spare Parts Budget 2015','Accessories')) as new

FROM [C:\Users\A637985\Downloads\All Brands Forecast_Budget_2015.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Audi);

The new is not reading once after the loading.

[Vegar]

I used the following script to create this pivot i QlikView.

 

MAP_NEW:
MAPPING LOAD * INLINE [
DNP sales, Class
Budget 2015-AGP, Spare
Budget 2015-AGA, Accessories
];

CrossTable (Month, Amount, 3)
LOAD 
	'Audi' as Brand,
	[DNP sales], 
     ApplyMap('MAP_NEW',[DNP sales]) as NEW, 
     Jan, 
     Feb, 
     Mar, 
     Apr, 
     May, 
     Jun, 
     Jul, 
     Aug, 
     Sep, 
     Oct, 
     Nov, 
     Dec
FROM
[All Brands Forecast_Budget_2015.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Audi);

[abhijith28]

Thanks a lot, it is working fine 🙂

[Vegar]

@abhijith28 how did it go? Did you manage to extract your data?

[Vegar]

Ah, we posted simultaneously 🙂
I'm glad it worked out for you.

[abhijith28]

yes, it worked 🙂