Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dears,
Can one always split a QVD in different parts (i.e. based on year critera) and afterwards gather the QVD's back in one table?
To be concrete:
I have 2 tables: X and Y. Both are extracted in QVD's per year. This results in
(same for table Y)
We load both tables and their corresponding QVD's using a X_*.qvd and a Y_*.qvd.
Now, when performing a left join between both tables (Y: left join X), Only Y data from 2006 is merged.
Now, when not performing a left join (just creating the link between) between both tables (Y: left join X), all data is available as it should be.
Both tables are mapped one on one.
Ps: When only loading per period (remove the *, and change it in i.e. 2007), the correct data is loaded as well.
Any ideas what could create this difference?
Thanks!
Maarten
When you do a file wildcard load, the files are loaded one at a time. After the first Y file is loaded, unmatched rows of X will have all Y fields -- with null values. Thereafter the new fields will be involved in the join and never match. For example.
TabX:
Key, ValueX
1, A
2, B
3, C
Y2006.qvd
Key, ValueY
1, X
Y2007.qvd
Key, ValueY
2, Y
3, Z
LEFT JOIN (TabX) LOAD * FROM Y2006.qvd (qvd); gives
TabX:
Key, ValueX, ValueY
1, A, X
2, B, -
3, C, -
Thereafter, a join of Y2007.qvd will match no rows because matching Key rows have the unmatching ValueY=- .
The only way to do this is to load all Y*.tables and then JOIN from the RESIDENT table.
When you do a file wildcard load, the files are loaded one at a time. After the first Y file is loaded, unmatched rows of X will have all Y fields -- with null values. Thereafter the new fields will be involved in the join and never match. For example.
TabX:
Key, ValueX
1, A
2, B
3, C
Y2006.qvd
Key, ValueY
1, X
Y2007.qvd
Key, ValueY
2, Y
3, Z
LEFT JOIN (TabX) LOAD * FROM Y2006.qvd (qvd); gives
TabX:
Key, ValueX, ValueY
1, A, X
2, B, -
3, C, -
Thereafter, a join of Y2007.qvd will match no rows because matching Key rows have the unmatching ValueY=- .
The only way to do this is to load all Y*.tables and then JOIN from the RESIDENT table.
Hi Rob,
Thanks a lot for your quick reply.
When thinking about your example, it makes sense (and even looks obvious behaviour :-)). We'll give this one a try.
Thanks again!
Maarten