topic Re: Merging / Replacing Data in QlikView

Merging / Replacing Data

shane_spencer — Wed, 22 Jun 2016 12:23:58 GMT

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?

Re: Merging / Replacing Data

kristof_j — Wed, 22 Jun 2016 12:42:33 GMT

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.

Re: Merging / Replacing Data

Gysbert_Wassenaar — Wed, 22 Jun 2016 12:43:18 GMT

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$);