Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Does not seem to find the Field.
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
Actually I didn't say that quite right.
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.