3 Replies Latest reply: Jan 15, 2014 1:19 AM by Kiran Rode RSS

    Pivot table populate missing strings

    Stanislav 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 :

      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 ?

        • 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

          • Re: Pivot table populate missing strings
            Kiran Rode

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