Pivot table populate missing strings

[Chernov]

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 :

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:

Who can help ?

[#2]

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]

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]

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