topic Re: Store in a QVD in QlikView

Store in a QVD

Thu, 12 Apr 2012 16:25:48 GMT

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 — Thu, 12 Apr 2012 16:33:54 GMT

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

Thu, 12 Apr 2012 17:19:35 GMT

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

cesaraccardi — Thu, 12 Apr 2012 17:39:12 GMT

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

Thu, 12 Apr 2012 19:02:31 GMT

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

Thank you

Store in a QVD

adhudson — Fri, 13 Apr 2012 00:23:24 GMT

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

Fri, 13 Apr 2012 01:58:38 GMT

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

adhudson — Fri, 13 Apr 2012 02:42:04 GMT

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

Fri, 13 Apr 2012 03:35:30 GMT

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

adhudson — Fri, 13 Apr 2012 03:54:54 GMT

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,

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

Fri, 13 Apr 2012 04:30:42 GMT

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

Re: Store in a QVD

adhudson — Fri, 13 Apr 2012 05:13:57 GMT

Hi,

Attached the file.

This code should work now.

Regards

Andrew Hudson

Re: Store in a QVD

Fri, 13 Apr 2012 13:39:59 GMT

Hey Andrew,

Thanks for your help.

I have treid the new code but it is not working.

It starts loading and freezes while loading then I get a failure to load message.

Is there anyway I can load all of this without Left join Qvd3 to QVD1?

Again thanks for spending the time helping me.

Regards,

Badr

Re: Store in a QVD

adhudson — Sat, 14 Apr 2012 06:19:20 GMT

Hi,

The script is failing because, the data is huge and doing the joins will take high amount of your RAM.

Just limit the no of rows to 10000 by adding where rowno()<10000 in every load.

Check whether it is storing the values in one qvd.

After that proceed with complete set of data.

Regards

Andrew Hudson

Re: Store in a QVD

Sat, 14 Apr 2012 17:39:06 GMT

Hi Andrew,

If I do that then I won't get all the data!

while I need all of it

Regards,