Re: How to upload Excel Pivot which is grouped at ... - Qlik Community

Anonymous · ‎2018-10-06

Hi All,

I have a sample pivoted excel file, and want to read it in QlikView and need to do reporting on the same.

Sample pic of data is given below and also attached the same here in this post.

MODEL	NORTH			SOUTH			HO			India
MODEL	DEL	GGN	North	HYD	BNG	South	OEM	KOEL	UIL	India
EP1000N	12	4	402	33	60	318	0	0	0	1196
EX 2400	1	0	36	3	15	63	0	0	0	129
EX 2400 S	2	0	13	4	20	59	0	0	0	98
EU 30 iN	6	0	59	4	40	184	0	0	0	318
EU 30 isN	24	1	46	16	40	216	0	0	0	326
EU70isN	25	0	46	7	45	147	0	0	0	227
Total Genset	70	5	602	67	220	987	0	0	0	2294
	0	0	0
LM - HRJ 196	4	0	15	0	0	2	0	0	0	60
LM - HRJ 216	1	1	8	4	8	20	0	0	0	45
B.C. - UMK 435 U2ST	0	0	10	0	0	0	0	0	0	10
B.C. - UMK 425 U2ST	0	0	6	12	10	52	0	0	0	85
B.C. - UMK 435 UEDT	0	0	7	0	0	10	0	0	0	17

I have data like mentioned above....

Can you please let me know how to load it in qlikview and do dashboards based on it.

you can please refer sample data which is attached with this post.

Thanks,

Qliker

qliknerd · ‎2018-10-06

Hi Ravi

You can use the CrossTable function to flatten all the columns. If you want the specific groupings to remain, might be easier to create as inline tables and load against the flattened data. Something like this:

Regions:

LOAD * INLINE [

AREA, REGION

DEL, NORTH

JK, NORTH

WUP, NORTH

GGN, NORTH

CHD, NORTH

JPR, NORTH

LKO, NORTH

PAT, EAST

RAN, EAST

BBSR, EAST

CAL, EAST

GUW, EAST

BPL, WEST

AHM, WEST

MUM, WEST

HYD, SOUTH

MDS, SOUTH

COC, SOUTH

BNG, SOUTH

OEM, HO

KOEL, HO

UIL, HO

];

Models:

LOAD * INLINE [

MODEL, MODELTYPE, MODELGROUP

EP1000N, Genset,

EX 2400, Genset,

EX 2400 S, Genset,

EU 30 iN, Genset,

EU 30 isN, Genset,

EU70isN, Genset,

LM - HRJ 196, C.B.U.,

LM - HRJ 216, C.B.U.,

B.C. - UMK 435 U2ST, C.B.U.,

B.C. - UMK 425 U2ST, C.B.U.,

B.C. - UMK 435 UEDT, C.B.U.,

B.C. - UMK 435T U2NT, C.B.U.,

B.C. - UMK 435T UENT, C.B.U.,

TLR - FJ 500, C.B.U.,

TLR - F 300, C.B.U.,

WJR 2525 GCD, C.B.U.,

GK 100, ENGINE SALES - (HSPP), A.1. G ENGINE

GK 200, ENGINE SALES - (HSPP), A.1. G ENGINE

G 200, ENGINE SALES - (HSPP), A.1. G ENGINE

GK200(W), ENGINE SALES - (HSPP), A.1. G ENGINE

G 300, ENGINE SALES - (HSPP), A.1. G ENGINE

GK 300 Q, ENGINE SALES - (HSPP), A.1. G ENGINE

GX 80, ENGINE SALES - (HSPP), A.2. G ENGINE

GX 160D QX (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

GX 160D QTB (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

GX 160D QKA, ENGINE SALES - (HSPP), A.2. G ENGINE

GX 200D QX (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

GX 200D QTB (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

GX 200D SD (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

GX 160D LHB3 (LTOB), ENGINE SALES - (HSPP), GX ENGINE - LTOB

GX 160D LKA, ENGINE SALES - (HSPP), GX ENGINE - LTOB

WBK 15, PUMPS,

WBK 30, PUMPS,

WBK 30FF, PUMPS,

WB 30X, PUMPS,

WSK 2020, PUMPS,

WMK 2520, PUMPS,

WM 2520X, PUMPS,

WMK 3025, PUMPS,

WM 3025X, PUMPS,

WMK 3025(3), PUMPS,

WPK-20FF, PUMPS,

WB 15 X, PUMPS,

WS 20 X, PUMPS,

WV30 D, PUMPS,

];

Sales:

CrossTable(AREA, VOLUME, 1)

LOAD

F2 as MODEL,

DEL,

JK,

WUP,

GGN,

CHD,

JPR,

LKO,

//North,

PAT,

RAN,

BBSR,

CAL,

GUW,

//East,

BPL,

AHM,

MUM,

//West,

HYD,

MDS,

COC,

BNG,

//South,

OEM,

KOEL,

UIL //,

//India

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1555598-340319/Sample%20Sale%20Data.xlsx]

(ooxml, embedded labels, header is 3 lines, table is APR)

Where Not Match(F2, 'GRAND TOTAL','SUB TOTAL','TOTAL','TOTAL (DOM)','Total Genset','C.B.U.','TTL - G ENGINES','TTL - GX ENGINES','PUMPS','M - means this month');

! !

Anonymous · ‎2018-10-06

Hi Ian Tsang,

You created this manually right!!!

But for every upload we need to go manual intervention for data update in qlikview, that is not good and data may miss/wrong while entering. and need manpower aswell time taken process.

Thank you for approach, but still looking for as simple reloading way without touching qlikview App.

Thanks,

Ravi

qliknerd · ‎2018-10-06

No problem - why would you need manual intervention? The Inline tables are created once to define the dimension groups needed for subtotalling purposes. If the columns and rows are likely to change depending on the source file (e.g. different models, areas or regions), then you could load all columns and rows. However, you will bring in the total/subtotal columns and rows that still have to be dealt with.

Sales:

CrossTable(AREA, VOLUME, 1)

LOAD

F2 as MODEL,

*

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1555598-340319/Sample%20Sale%20Data.xlsx]

(ooxml, embedded labels, header is 3 lines, table is APR)

;

Anonymous · ‎2018-10-07

Hi Ian,

Sorry for inconvenience, can you please tell me how to do that, and when user do the changes in excel file, it has to pic the same excel file data to qlikview...

Can you please provide steps that you followed as a layman understanding purpose.

Thanks,

Ravi

How to upload Excel Pivot which is grouped at Multilevel