Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

How to create below table

Hi Gurus,

How to create below sales table.

gwassenaarhicstalwar1

 

Sales Details

Required Format
CompanyPartMonthValue CompanyPartQ1Q2Q3
aP1Q180 aP1+P21229969
aP2Q142 P3+P4120152161
aP3Q182 P5239129
aP4Q138 Total265342259
aP5Q123 bR3+R59213687
aP1Q240 R1+R4159137139
aP2Q259 R2976684
aP3Q271 Total348339310
aP4Q281
aP5Q291
aP1Q341
aP2Q328
aP3Q395
aP4Q366
aP5Q329
bR1Q196
bR2Q197
bR3Q169
bR4Q163
bR5Q123
bR1Q284
bR2Q266
bR3Q251
bR4Q253
bR5Q285
bR1Q381
bR2Q384
bR3Q349
bR4Q358
bR5Q338   

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

4 Replies
Highlighted
Honored Contributor II

Re: How to create below table

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;

Capture.JPG

Capture.JPG

Highlighted
Contributor

Re: How to create below table

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

Highlighted
Honored Contributor II

Re: How to create below table

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;

Highlighted

Re: How to create below table

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;