How to create below table

[narayanamsn]

Hi Gurus,

How to create below sales table.

gwassenaar‌hic‌ stalwar1‌

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

[qlikviewwizard]

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;

[narayanamsn]

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

[qlikviewwizard]

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;

[Kushal_Chawda]

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;