Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

schernov
New Contributor III

Pivot table populate missing strings

i create sample script:

Shops:

load RecNo() as ShopID,

* Inline

[ShopName

Shop1

Shop2

Shop3

Shop4

Shop5];

Sales:

Load * Inline

[ShopID, RefCode, Price, Qnty, Date

1, 4862, 25.30, 7, 01.12.2013

1, 4862, 25.30, 8, 02.12.2013

1, 4862, 25.30, 9, 03.12.2013

1, 4862, 25.30, 10, 04.12.2013

1, 5609, 25.30, 9, 03.12.2013

1, 5609, 25.30, 10, 04.12.2013

1, 4862, 29.60, 11, 05.12.2013

2, 4862, 25.30, 7, 01.12.2013

2, 4862, 25.30, 8, 02.12.2013

2, 5610, 25.30, 9, 03.12.2013

2, 5610, 25.30, 10, 04.12.2013

2, 4862, 29.60, 11, 05.12.2013

3, 4862, 25.30, 7, 01.12.2013

3, 4862, 25.30, 8, 02.12.2013

3, 40030, 25.30, 9, 03.12.2013

3, 40030, 25.30, 10, 04.12.2013

3, 4862, 29.60, 11, 05.12.2013

4, 4862, 25.30, 7, 01.12.2013

4, 4862, 25.30, 8, 02.12.2013

4, 4862, 25.30, 9, 03.12.2013

4, 4862, 25.30, 10, 04.12.2013

4, 4862, 29.60, 11, 05.12.2013

];

Ref:

First 1500

LOAD СегментКод as GroupCode,

     СегментНаименование as GroupName,

     НоменклатураКод as RefCode,

     НоменклатураНаименование as RefName

FROM Data\Справочники\Ном.qvd(qvd);

and make pivot table in sheet :

Clipboard03.jpg

Q1 : i want see "Shop5" in First Group - "100 СЫРЬЕ ПРОИЗВОДСТВО", how i can do this ? Sales by Shop5 - NULL!!

Q2 : and, i want to see all shop's in other group's without data, but with zero like this:

Clipboard02.jpg

Who can help ?

3 Replies
Not applicable

Re: Pivot table populate missing strings

HI Stanislav,

  Please find the attachment for Q1 solution.

For Q2. in your base data (SalesTable) doesn't have Ref Codes for some of the groups. if you can fill those automatically you can get all shops for all groups. Otherwise do the Right join with the Ref table automatically you will get that.

Santhosh G

schernov
New Contributor III

Re: Pivot table populate missing strings

it is not - =if(GroupName='100 СЫРЬЕ ПРОИЗВОДСТВО' and Len(ShopName)=0,'Shop 5',ShopName)

in my real model i have 50+ shops, with dynamic data updates, more then 200Mb data every day, and 1,000+ reference groups.

kiranmanoharrode
Contributor III

Re: Pivot table populate missing strings

Dear Stanislav,

               Just Join tables Shops and Sales. You will get result which you mentioned above.

for more detail execute below script.

/////////////////////////////////////////////////////Script/////////////////////////////////////////////////

load RecNo() as ShopID,

*
Inline

[ShopName

Shop1

Shop2

Shop3

Shop4

Shop5]
;



Sales:

Join(Shops)

Load * Inline

[ShopID, RefCode, Price, Qnty, Date

1, 4862, 25.30, 7, 01.12.2013

1, 4862, 25.30, 8, 02.12.2013

1, 4862, 25.30, 9, 03.12.2013

1, 4862, 25.30, 10, 04.12.2013

1, 5609, 25.30, 9, 03.12.2013

1, 5609, 25.30, 10, 04.12.2013

1, 4862, 29.60, 11, 05.12.2013

2, 4862, 25.30, 7, 01.12.2013

2, 4862, 25.30, 8, 02.12.2013

2, 5610, 25.30, 9, 03.12.2013

2, 5610, 25.30, 10, 04.12.2013

2, 4862, 29.60, 11, 05.12.2013

3, 4862, 25.30, 7, 01.12.2013

3, 4862, 25.30, 8, 02.12.2013

3, 40030, 25.30, 9, 03.12.2013

3, 40030, 25.30, 10, 04.12.2013

3, 4862, 29.60, 11, 05.12.2013

4, 4862, 25.30, 7, 01.12.2013

4, 4862, 25.30, 8, 02.12.2013

4, 4862, 25.30, 9, 03.12.2013

4, 4862, 25.30, 10, 04.12.2013

4, 4862, 29.60, 11, 05.12.2013

]
;



Ref:

First 1500

LOAD СегментКод as GroupCode,

    
СегментНаименование as GroupName,

    
НоменклатураКод as RefCode,

    
НоменклатураНаименование as RefName

FROM Data\Справочники\Ном.qvd(qvd);

Community Browser