Load rows only matching combination of two fields from qvd

[tm_]

Hello,

I have a qlikview script where I have one table called findmoq1 with 90 000 rows with columns

PN,

Period,

I want to match these on PN and Period from a bigger qvd file with over 30 million rows and read in a specific field called [Pall Qty] Now I use where exists but then I get all 30 million rows read in which takes a long time to load although the results seem correct.

Here is how I write it:

Inner Join(findmoq1)
test:
LOAD [Part No],
Period,
[Pall Qty] as [Pall qty when latest received]
FROM
[Z:\Alldata2_VO.qvd]
(qvd) Where Exists([Part No], [Part No]) And Exists(Period, Period);

Can you help me make a more selective load so that I only read in those 90 000 rows from the bigger qvd file in the first place so that the load goes faster?

Thanks!

[sunny_talwar]

You can optimize your load, but for doing that you can only do a single Where Exists. Right now you already have 2 Exists and you want to add the third one to it? Are all of these fields (Part No, Period and Pall Qty) coming from a single table which is loaded before test table?

[tm_]

Hello,

No those fields are coming from the qvd file. I only want to read in the Pall qty field where the combination Part No and Period matches as in the table findmoq1. Do I need to create a key for that and use one where exists?

BR Thomas

[anderseriksson]

If you have PartNo and Period already in a resident table you don't need to check with Exists.
Simply do the inner join and only those rows with matching PartNo and Period will be read.

[tm_]

Hello,

This is what I thought as well but looking at the script execution load it still says that it is reading in all those lines, please see picture attached.

Here is my full script:

ODBC CONNECT32 TO [MS Access Database;DBQ=Z:\database.accdb];
SQL SELECT "Latest_Received_Date",
"Latest_Received_Qty",
"Part Number" as "Part No"
FROM "Latest Received";

findmoq1:
Load
Left("Latest_Received_Date",4)*100 + Ceil(mid("Latest_Received_Date",5,2)/4) as Period,
[Part No]
Resident [Latest Received];

Inner Join(findmoq1)
LOAD [Part No],
Period,
[Pall Qty] as [Pall qty when latest received]
FROM
[Z:\Alldata2_VO.qvd]
(qvd);

Isn't there a way to only read in those 90 000 rows to begin with?

[marcus_sommer · Accepted solution]

If you really want to use an optimized load for the join-qvd (it is already optimized) which only loads the records of fieldvalues which already exist in previous loadings you need to create a combined-key from [Part No] and [Period] within the qvd and also within the findmoq1-table because only a where-exists approach with a single parameter won't break the optimized load.

To reduce the loading-times it might in your case enough just to use one field within the where-exists because the inner join itself will do the matching. I mean something like this:

ODBC CONNECT32 TO [MS Access Database;DBQ=Z:\database.accdb];
findmoq1:
Load *, Left("Latest_Received_Date",4)*100 + Ceil(mid("Latest_Received_Date",5,2)/4) as Period;
SQL SELECT "Latest_Received_Date", "Latest_Received_Qty", "Part Number" as "Part No"
FROM "Latest Received";

Inner Join(findmoq1)
LOAD [Part No], Period, [Pall Qty] as [Pall qty when latest received]
FROM [Z:\Alldata2_VO.qvd] (qvd) where exists(Period);

- Marcus