topic Re: Qlikview Loading. in QlikView

Qlikview Loading.

abhijith28 — Wed, 14 Nov 2018 08:13:38 GMT

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.

Re: Qlikview Loading.

prieper — Wed, 14 Nov 2018 08:18:19 GMT

Plse check CROSSTABLE-functionality,

Re: Qlikview Loading.

techvarun — Wed, 14 Nov 2018 08:19:54 GMT

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
];

Re: Qlikview Loading.

Vegar — Wed, 14 Nov 2018 08:24:16 GMT

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

Re: Qlikview Loading.

abhijith28 — Wed, 14 Nov 2018 08:26:09 GMT

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.

Re: Qlikview Loading.

Vegar — Wed, 14 Nov 2018 11:39:54 GMT

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);

Re: Qlikview Loading.

abhijith28 — Wed, 21 Nov 2018 09:43:36 GMT

Thanks a lot, it is working fine 🙂

Re: Qlikview Loading.

Vegar — Wed, 21 Nov 2018 09:43:53 GMT

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

Re: Qlikview Loading.

Vegar — Wed, 21 Nov 2018 09:45:16 GMT

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

Re: Qlikview Loading.

abhijith28 — Wed, 21 Nov 2018 11:05:55 GMT

yes, it worked 🙂