topic Re: Pivot table populate missing strings in QlikView

Pivot table populate missing strings

Chernov — Wed, 15 Jan 2014 05:05:39 GMT

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 ?

Re: Pivot table populate missing strings

Wed, 15 Jan 2014 05:51:01 GMT

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

Re: Pivot table populate missing strings

Chernov — Wed, 15 Jan 2014 06:16:05 GMT

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.

Re: Pivot table populate missing strings

kiranmanoharrode — Wed, 15 Jan 2014 06:19:32 GMT

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