Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Chernov
Creator
Creator

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

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

Chernov
Creator
Creator
Author

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
Creator III
Creator III

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);