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

                     

                    • Re: Store in a QVD
                      Badr Harfoush

                      Hi again,

                      I tried that but it didnt help..

                      I left the Laoding from the qvds as it (loading from 3 different qvds)and added a name to the month  map and used it the same way you have told me. then I uncommented the Eng tab on the script ..

                      I am still getting the same error..

                      See attached

                      Man, I have to submit this project tomorrow and don't know what to do!

                      I appreciate any possible help.

                      Thxs