Where Exists

[#1]

Hi Everyone,

I have something to learn from you all.

I have two qvds and- History & Present.

In History we store the monthly snapshots and Present holds the current details.

For the Product details we need to Join them with Product qvd.

Now the requirement is to find the number of missing products from History to

Present.

There are many items which were in pending stage, and the related(corresponding) product

numbers were stored in History qvd but with due course of time, those products got

cancelled and hence later on the Present qvd we don't have those details.

SO, now my task is to find how many of those products are missing.

I guess we need to use WHERE Exists, can someone suggest me what should I do next.

Thanks and Regards,

Bikash

[stevedark · Accepted solution]

If I follow your description correctly, you want to load the records from History where the products are missing so that you can re-instate them?

The code will be something like:

Temp_ProductID:

LOAD

     ProductID

FROM Product.qvd (qvd);

History:

LOAD
     *

FROM History.qvd (qvd)

WHERE NOT EXISTS (ProductID);

DROP TABLE Temp_ProductID;

You can then interrogate the loaded rows to try and work out what is missing.

If I have misread your requirements then please post back with more details.

- Steve

[#3]

Hi Steve,

Thank you so much for the answer.

Could you please tell how does it find the missing products, as because neither my History nor Present QVD has the field "Product ID". Its there in Product QVD. Product QVD has 11 fields and History & Present has the same 86 fields.

Part ID, which is different from Product ID is common in these three QVDs.

How do I join them first. Only then I guess I would be able to follow your steps.

Thanks again

Regards,

Bikash

[stevedark]

Sorry, I missed the issue slightly.  It will be simpler to load all three QVDs (the two identical ones should concatenate) and then insert a List Box with the Part ID in it. From here enter the following search expression:

=sum(if(isnull([Product ID]), 1, 0)) > 0

You will need to ensure you delete the ** wildcard characters that appear by default on the search criteria.

This should then select only rows where there is no Product ID.

Alternatively I think that if you use the WHERE EXISTS syntax from above, but replace ProductID with [Part ID] it shold only load the rows with issues.

Regards,

Steve

[#5]

Steve can you tell me how to write the code to concatenate.

Because both the identical QVDs has exactly the same field names.

will there be issues of synthetic key?

Thanks & Regards,

Bikash

[stevedark]

If you load exactly the same columns from both QVD's they will automatically concatenate.  It is always a good idea to do a Limited Load with just a few rows (found under Debug in the script editor) as QlikView can hang if you load two tables with almost the same columns.

You can also force a concatenate by placing a CONCATENATE statement between the two loads.  If the columns are not exactly the same though a non-optimized QVD load will be performed.  This will be much slower.  Search the community for more info on optimized / non-optimized loads.

Regards,
Steve

[#7]

Thanks Steve with your suggestion I can load both the identical QVDs History & Present.

But I have a Product qvd. First a left join has to be performed on History and Product QVDs

to get the product details with the History and then I also have to do a left join on Present

and Product QVD.

Could you please tell me how do i do that with running into synthetic keys.

If I can achieve the above then I can perform the Where Exists clause that you suggested.

Thanks again,

Bikash

[stevedark]

Surely if both Present and History have loaded into a single table, and the only common key between those and the Product table is the [Part ID] then there would be no issue with synthetic keys?

Could you perhaps do a Limited Load of 100 rows or so and then post up a screenshot of the resulting table structure?

- Steve

[#9]

Okay Steve I am working on it. I will post that once I can achieve it.

You have a nice time,

Regards,

Bikash

[#10]

hi! try something like this...

LOAD

A & '-' & B AS AB,

A,

B,

C

from TABLE1;

LOAD

*

WHERE EXISTS(AB);

LOAD

A & '-' & B AS AB,

D,

E

from TABLE2;

I made this ... has someone found another solution?

probably have! because in this way i load 2 times the same table to get result.. so ... anybody????

thank you!!!

bikashdebnath ... I sincerely hope have helped you

André Mussi