14 Replies Latest reply: Apr 14, 2012 1:39 PM by Badr Harfoush RSS

    Store in a QVD

    Badr Harfoush

      Hi,

      Anybody knows how to store all the below in one QVD

       

       

      QVDLoad1:

       

      LOAD *,

          applymap('Manufact_Map', MANUFACTURERABBR,'Other' ) as ManufactLabel;

       

       

       

      LOAD * from ..\QVD\ItemMaster.qvd(qvd);

      Left Join(QVDLoad1)

      Load * Inline [

      MONTH,MonthName

      1, Jul

      2, Aug

      3, Sep

      4, Oct

      5, Nov

      6, Dec

      7, Jan

      8, Feb

      9, Mar

      10, Apr

      11, May

      12, Jun

      ];

      LOAD *,

          applymap('ManagerName_Map', MANAGER2, 'Other') as ManagerName;

       

      LOAD *,

          applymap('Department_Map', MANAGER2, 'Other') as Department;

       

          LOAD *,

          applymap('Division_Map', MANAGER2, 'Other') as Division;

       

       

      LOAD * from ..\QVD\ShipItem.qvd(qvd);

      LOAD * from ..\QVD\Putbom.qvd(qvd);

       

       

      I tried Store QVDLoad1 into ByBrand.qvd(qvd);

      but it didn't pick up everything

        • Store in a QVD
          Celambarasan Adhimulam

          Hi,

               It store only the result of this part

          LOAD *,

              applymap('Manufact_Map', MANUFACTURERABBR,'Other' ) as ManufactLabel;

           

           

           

          LOAD * from ..\QVD\ItemMaster.qvd(qvd);

          Left Join(QVDLoad1)

          Load * Inline [

          MONTH,MonthName

          1, Jul

          2, Aug

          3, Sep

          4, Oct

          5, Nov

          6, Dec

          7, Jan

          8, Feb

          9, Mar

          10, Apr

          11, May

          12, Jun

          ];

           

          because other things not come under QVDLoad1 table.

            • Store in a QVD
              Badr Harfoush

              That's true..

              I am looking for a way to store all the above data in one QVD.

              Anybody can help?

              Thxs

                • Store in a QVD
                  Cesar Accardi

                  Hi, you can only store one table in a QVD file, that means you must find a way to keep all this info in one table. You should look for common fields or keys to make the joins and finally store it in a QVD file.

                   

                  Regards,
                  Cesar

                    • Store in a QVD
                      Badr Harfoush

                      Would you please give me more details on how to do that?

                      Thank you

                        • Store in a QVD
                          Andrew Hudson

                          Hi,

                           

                               Can you share some sample data and some qvd files?

                           

                               So that we can resolve this issue.

                           

                          Regards

                          Andrew Hudson

                            • Re: Store in a QVD
                              Badr Harfoush

                              Hi,

                              Thank you for your help.

                              Attached is a folder with the document and its sources..

                              I was trying to avoid a circular issue that is happening after adding the last tab in the script which is CostEng table that is loading from excel..

                              If I comment this table everything work fineand the data is correct ..once I load it with the other qvds tables I get error...

                              I appreciate any help.

                              Thxs

                                • Re: Store in a QVD
                                  Andrew Hudson

                                  Hi,

                                   

                                       Please find the attached file for the solution.

                                   

                                       I have taken STN as the common field between all the three tables.

                                   

                                       After doing the left join with ItemMaster table, all the tables has been merged and stored in a single qvd.

                                   

                                       Mappings can be done after the execution of left joins. It will give you the same result.

                                   

                                       Check if it is appropriate to your requirement.

                                   

                                       Note:

                                       I pulled only first 10000 rows from all the qvds since the no of rows are more than million. It is taking sometime to do the left join

                                   

                                       Hope this helps

                                   

                                  Regards

                                  Andrew Hudson.

                                    • Re: Store in a QVD
                                      Badr Harfoush

                                      Hi Andrew,

                                      Thank you for the quick replay.

                                      the Qvd looks fine but it didn't fix my issue as I was expected..

                                      My main problem is as follow:

                                      If you go back to the document that I sent "ByBrandName.qvw"..

                                      Then if youcomment the last tab on the script then everything work fine and correct..

                                      this last tab hold additional cost per specific month..(See the CostEng1.xls)..

                                      When I uncomment this tab and load I get circualr error because the excel file(CostEng1.xls) has FY, MonthName Manufactlbl and EngCost....

                                      as the first three column are available on other tables that created an error..

                                      Is there anyway you think I can fix that..

                                      Note that loading the data from the document that I sent without the last script tab takes less than a min to load

                                      I appreciate your help and your time.

                                      Thxs,

                                        • Re: Store in a QVD
                                          Andrew Hudson

                                          Hi,

                                           

                                               The cirsular reference error or loop error is caused because in the main tab, while creating a inline table with MONTH and MonthName fields, you need to give a table name for that and use that as the mapping table. For clear idea, See the script below.

                                              

                                          MonthMap:

                                          Mapping

                                          Load * Inline [

                                          MONTH,MonthName

                                          1, Jul

                                          2, Aug

                                          3, Sep

                                          4, Oct

                                          5, Nov

                                          6, Dec

                                          7, Jan

                                          8, Feb

                                          9, Mar

                                          10, Apr

                                          11, May

                                          12, Jun

                                          ];

                                           

                                          After this, you can use this Mapping table in ShipItem table as I have used in the script.

                                           

                                          ShipItem:

                                          Left Join(ItemMaster)

                                          LOAD ITEMPRICE,

                                               STN,

                                               SALESPRICE,

                                               MANAGER2,

                                               ApplyMap('MonthMap',MONTH) AS MONTH,

                                               FY

                                          FROM

                                          ShipItem.qvd

                                          (qvd)

                                          Where RowNo()<=10000;

                                           

                                          After using this, you wont get any circular reference error.

                                           

                                          Regards

                                          Andrew Hudson