topic Where Exists in QlikView

Where Exists

Tue, 07 Jun 2011 17:12:35 GMT

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

Where Exists

stevedark — Tue, 07 Jun 2011 21:39:38 GMT

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

Where Exists

Tue, 07 Jun 2011 21:47:24 GMT

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

Where Exists

stevedark — Tue, 07 Jun 2011 22:01:29 GMT

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

Where Exists

Tue, 07 Jun 2011 22:07:43 GMT

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

Where Exists

stevedark — Tue, 07 Jun 2011 22:26:51 GMT

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

Where Exists

Tue, 07 Jun 2011 22:32:22 GMT

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

Where Exists

stevedark — Tue, 07 Jun 2011 22:38:01 GMT

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

Where Exists

Tue, 07 Jun 2011 22:39:37 GMT

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

You have a nice time,

Regards,

Bikash

Where Exists

Fri, 26 Aug 2011 13:19:04 GMT

hi! try something like this...

LOAD

A & '-' & B AS AB,

from TABLE1;

LOAD

WHERE EXISTS(AB);

LOAD

A & '-' & B AS AB,

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

Where Exists

Fri, 26 Aug 2011 17:27:59 GMT

Hi Everyone,

Instead of where exists what I did was a left join.

Which gave me an instant solution to my problem.

Thanks a lot everyone.

With best regards,

Bikash

Where Exists

rohit214 — Mon, 29 Aug 2011 12:33:11 GMT

hiii..

hw can i use Where clause in my script....plz give an detail example

Re: Where Exists

Mon, 29 Aug 2011 18:40:54 GMT

Hi,

I have attached a simple example for you.

Hope this helps,

Please feel free if you have any further queries.

Regards,

Bikash

Where Exists

rohit214 — Tue, 30 Aug 2011 06:24:46 GMT

hi...sir

thaanx for helping me ..i git ur attachment ..is helps me a lot.

now i understood hw to use.

Re: Where Exists

Mon, 10 Jul 2017 10:14:17 GMT

It's great your question has been answered...I have created a white paper on this topic and hopefully it will help broaden individual's knowledge on the topic. There are multiple ways to limit the data load which are included.

Ever have the issue of loading in data which does not really need to exist in your data model?

Would you like to maybe load in new order details which aren’t linked to a previous order ID?

Would you like to load in transactional data based on the data that already exists within your data model?

Functions exist in the QlikView script which allows the user to force only certain values to be brought in/or left out. This powerful feature can support in controlling the amount of data your qvw is holding – helping in fine tuning the performance of your dashboard and productionising your application.

Thanks,

Ricky