Create QVD's per year?

[#1]

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

• X_2006.qvd,
• X_2007.qvd,
• X_2008.qvd,
• X_2009.qvd,
• X_2010.qvd

(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

[rwunderlich · Accepted solution]

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.

[#3]

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