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

[stevietm]

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

[Anil_Babu_Samineni]

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)

[stevietm]

Does not seem to find the Field.

[Anonymous]

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

[Anonymous]

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.

[balabhaskarqlik]

Try with these:

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

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