Merging / Replacing Data

[shane_spencer]

I've got 3 sources of data (see attached).

My Primary source is "data_1.xls" that contains a list of all my servers. I need additional fields that I get from a very large source "data_2.xls" (the attached is just a sample the real files contains thousand of rows) by doing a LEFT JOIN. So far so good, see attached qvw.

The problem is that not all the Servers that are in data_1.xls are in data_2.xls. Therefore I need to load an additional file with this missing data "data_3.xls" to fill in the blank rows. How do I do this?

[kristof_j]

Does this work?

With firstsortedvalue only 1 record will be kepted.

And if level_data = 1 then that record will remain.

If there is nog level_data = 1 then level_data = 2 will be taken.

[Gysbert_Wassenaar · Accepted solution]

First concatenate data2 and data3 then right join the result of that to data1:

Result:

LOAD ...some_fields... FROM data_2_sample.xls (biff, embedded labels, table is Sheet1$);

CONCATENATE (Result)

LOAD ...some_fields... FROM data_3.xls (biff, embedded labels, table is Sheet1$);

RIGHT JOIN (Result)

LOAD ...some_fields... FROM data_1.xls (biff, embedded labels, table is Sheet1$);