Store in a QVD

[#1]

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

[CELAMBARASAN]

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.

[#3]

That's true..

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

Anybody can help?

Thxs

[cesaraccardi]

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

[#5]

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

Thank you

[adhudson]

Hi,

     Can you share some sample data and some qvd files?

     So that we can resolve this issue.

Regards

Andrew Hudson

[#7]

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

[adhudson]

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.

[#9]

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,

[adhudson]

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