topic Re: Loading data from qvd with where clause from another qvd or resident load in QlikView

Loading data from qvd with where clause from another qvd or resident load

stevietm — Mon, 02 Jul 2018 15:47:19 GMT

Hi All,

As the title states i want to load data from a qvd with a where clause that only pulls data from another qvd where the where clause is true.

E.g. of what i am looking for.

Temp_Table1:

Load

Key,

Date,

sales

from a.qvd;

Table2:

Load

Key,

Date,

sales

from b.qvd where Key = resident Temp_Table1.key;

drop table

Reason why i want to do it this way is because my second qvd only has about 100k lines and the first one has 50 mil but only want to extract the matching 100k.

Not sure if this is possible or if there is another way around this...

Thanks in advance

Regards

Stevie

Re: Loading data from qvd with where clause from another qvd or resident load

Anil_Babu_Samineni — Mon, 02 Jul 2018 16:06:29 GMT

May be try this?

Qualify Key;

Temp_Table1:

Load

Key,

Date,

sales

from a.qvd;

Qualify Key;

Table2:

Load

Key,

Date,

sales

from b.qvd where Exists(Temp_Table1.Key,Table2.Key)

Re: Loading data from qvd with where clause from another qvd or resident load

stevietm — Mon, 02 Jul 2018 16:23:29 GMT

Does not seem to find the Field.

Re: Loading data from qvd with where clause from another qvd or resident load

Anonymous — Mon, 02 Jul 2018 21:27:47 GMT

Temp_Table1: (the smaller table with 100K rows)

Load

Key,

Date,

sales

from a.qvd;

left keep (Temp_Table1)

Table2: (50 mil rows)

Load

Key,

Date as Date2, (changed name so keep only uses Key to match)

sales as sales2, (changed name so keep only uses Key to match)

other_fields_you_want_from_big_file

from b.qvd

drop table TempTable1;

left makes all rows from first table included in resulting Temp_Table1

keep (tablename) makes QV look for and make a table (Table2) that just keeps matching rows in 2nd file using the common field, Key.

It will add rows if there are multiple matching rows in the 2nd file.

This example assumes the Key is enough to identify the rows you want in the big table.

If not, you need to make a column in both tables to use for this purpose, ex. Key & '-' & Date & '-' & sales as index. and then change the name of Key in one of the tables so QV doesn't try to match with it also.

Not the only way to do this.

I think you could also do a sub-query of small file to select Key and match large file key on that in the where clause.

John

Re: Loading data from qvd with where clause from another qvd or resident load

Anonymous — Mon, 02 Jul 2018 21:32:32 GMT

Actually I didn't say that quite right.

left makes all rows from first table included in resulting Temp_Table1

That's not what I meant to say. Actually it uses all the rows in Temp_Table1 and looks at the Key field there and then sees if there's a match in b.qvd. If there's a match, it keeps the row for Table2.

So Table2 just has rows that match the Temp_Table1 Key.

Re: Loading data from qvd with where clause from another qvd or resident load

BalaBhaskar_Qlik — Tue, 03 Jul 2018 15:32:00 GMT

Try with these:

https://community.qlik.com/thread/32944

https://community.qlik.com/thread/18826.