topic Re: How to create below table in QlikView

How to create below table

narayanamsn — Wed, 25 Nov 2020 16:16:04 GMT

Hi Gurus,

How to create below sales table.

Sales Details				Required Format
Company	Part	Month	Value	Company	Part	Q1	Q2	Q3
a	P1	Q1	80	a	P1+P2	122	99	69
a	P2	Q1	42		P3+P4	120	152	161
a	P3	Q1	82		P5	23	91	29
a	P4	Q1	38		Total	265	342	259
a	P5	Q1	23	b	R3+R5	92	136	87
a	P1	Q2	40		R1+R4	159	137	139
a	P2	Q2	59		R2	97	66	84
a	P3	Q2	71		Total	348	339	310
a	P4	Q2	81
a	P5	Q2	91
a	P1	Q3	41
a	P2	Q3	28
a	P3	Q3	95
a	P4	Q3	66
a	P5	Q3	29
b	R1	Q1	96
b	R2	Q1	97
b	R3	Q1	69
b	R4	Q1	63
b	R5	Q1	23
b	R1	Q2	84
b	R2	Q2	66
b	R3	Q2	51
b	R4	Q2	53
b	R5	Q2	85
b	R1	Q3	81
b	R2	Q3	84
b	R3	Q3	49
b	R4	Q3	58
b	R5	Q3	38

I tried using pivot table.

created measures and pulled them as rows, unable to understand how to hide measures which are not applicable to other comapnies.

Ex: R series part measures are applicable only to company B, hence do not want to show them for company A

Re: How to create below table

qlikviewwizard — Sun, 24 Jun 2018 05:25:27 GMT

Hi,

Try like this.

Data_Temp:
LOAD * INLINE [
Company,Part,Month,Value
a,P1,Q1,80
a,P2,Q1,42
a,P3,Q1,82
a,P4,Q1,38
a,P5,Q1,23
a,P1,Q2,40
a,P2,Q2,59
a,P3,Q2,71
a,P4,Q2,81
a,P5,Q2,91
a,P1,Q3,41
a,P2,Q3,28
a,P3,Q3,95
a,P4,Q3,66
a,P5,Q3,29
b,R1,Q1,96
b,R2,Q1,97
b,R3,Q1,69
b,R4,Q1,63
b,R5,Q1,23
b,R1,Q2,84
b,R2,Q2,66
b,R3,Q2,51
b,R4,Q2,53
b,R5,Q2,85
b,R1,Q3,81
b,R2,Q3,84
b,R3,Q3,49
b,R4,Q3,58
b,R5,Q3,38
];
JOIN (Data_Temp)
LOAD Company,'P1+P2' as Part,Month,sum(Value) as Value
Resident Data_Temp
where Part='P1' or Part='P2'
GROUP BY Company,Month;
JOIN (Data_Temp)
LOAD Company,'P3+P4' as Part,Month,sum(Value) as Value
Resident Data_Temp
where Part='P3' or Part='P4'
GROUP BY Company,Month;
JOIN (Data_Temp)
LOAD Company,'R3+R5' as Part,Month,sum(Value) as Value
Resident Data_Temp
where Part='R3' or Part='R5'
GROUP BY Company,Month;
JOIN (Data_Temp)
LOAD Company,'R1+R4' as Part,Month,sum(Value) as Value
Resident Data_Temp
where Part='R1' or Part='R4'
GROUP BY Company,Month;
NoConcatenate
DATA:
Load * Resident Data_Temp
where Not WildMatch(Part,'P1','P2','P3','P4','R1','R3','R4','R5');
DROP TABLE Data_Temp;

Re: How to create below table

narayanamsn — Mon, 25 Jun 2018 05:51:15 GMT

Thank you Arjun. Your solution had overwritten all the original part description and restated that with Part Groups. I will be unable to refer the individual Part description in the future expressions as you solution had restated the parts data with groups. Will you suggest another workaround to achieve this as the current solution cannot be used.

Regards,

Satya

Re: How to create below table

qlikviewwizard — Mon, 25 Jun 2018 07:59:34 GMT

Hi,

Try this.

Data_Temp:

LOAD * INLINE [

Company,Part,Month,Value

a,P1,Q1,80

a,P2,Q1,42

a,P3,Q1,82

a,P4,Q1,38

a,P5,Q1,23

a,P1,Q2,40

a,P2,Q2,59

a,P3,Q2,71

a,P4,Q2,81

a,P5,Q2,91

a,P1,Q3,41

a,P2,Q3,28

a,P3,Q3,95

a,P4,Q3,66

a,P5,Q3,29

b,R1,Q1,96

b,R2,Q1,97

b,R3,Q1,69

b,R4,Q1,63

b,R5,Q1,23

b,R1,Q2,84

b,R2,Q2,66

b,R3,Q2,51

b,R4,Q2,53

b,R5,Q2,85

b,R1,Q3,81

b,R2,Q3,84

b,R3,Q3,49

b,R4,Q3,58

b,R5,Q3,38

];

JOIN (Data_Temp)

LOAD Company,'P1+P2' as Part,Month,sum(Value) as Value

Resident Data_Temp

where Part='P1' or Part='P2'

GROUP BY Company,Month;

JOIN (Data_Temp)

LOAD Company,'P3+P4' as Part,Month,sum(Value) as Value

Resident Data_Temp

where Part='P3' or Part='P4'

GROUP BY Company,Month;

JOIN (Data_Temp)

LOAD Company,'R3+R5' as Part,Month,sum(Value) as Value

Resident Data_Temp

where Part='R3' or Part='R5'

GROUP BY Company,Month;

JOIN (Data_Temp)

LOAD Company,'R1+R4' as Part,Month,sum(Value) as Value

Resident Data_Temp

where Part='R1' or Part='R4'

GROUP BY Company,Month;

NoConcatenate

DATA:

Load * Resident Data_Temp ;

//where Not WildMatch(Part,'P1','P2','P3','P4','R1','R3','R4','R5');

DROP TABLE Data_Temp;

Re: How to create below table

Kushal_Chawda — Mon, 25 Jun 2018 08:25:30 GMT

Data_Temp:

LOAD *,

round(AutoNumber(RowNo(),Company&Month)/2) as Number INLINE [

Company,Part,Month,Value

a,P1,Q1,80

a,P2,Q1,42

a,P3,Q1,82

a,P4,Q1,38

a,P5,Q1,23

a,P1,Q2,40

a,P2,Q2,59

a,P3,Q2,71

a,P4,Q2,81

a,P5,Q2,91

a,P1,Q3,41

a,P2,Q3,28

a,P3,Q3,95

a,P4,Q3,66

a,P5,Q3,29

b,R1,Q1,96

b,R2,Q1,97

b,R3,Q1,69

b,R4,Q1,63

b,R5,Q1,23

b,R1,Q2,84

b,R2,Q2,66

b,R3,Q2,51

b,R4,Q2,53

b,R5,Q2,85

b,R1,Q3,81

b,R2,Q3,84

b,R3,Q3,49

b,R4,Q3,58

b,R5,Q3,38

];

New:

NoConcatenate

LOAD Company,

Month,

Number,

Concat(Part,'+') as Part,

sum(Value) as Value

Resident Data_Temp

Group by Company,Month,Number

Order by Company, Month,Part;

DROP Table Data_Temp;