A - B Load

[Anonymous]

Dear Experts

I have 2 Excel files and i want to load them in QlikView, but I only want to load the records which occored only once.

Lets suppose 1st file name is A and second file name is B so I want A - B functionality.

I dont want to load records which are in both tables, only those records should be loaded which appears only once in either File A or B.

any ideas?

Thanks

Waqas

[#2 · Accepted solution]

Hi.

Look at this example:

temp:

load F1,F2 from excel1.xls (biff, embedded labels, table is [Sheet1$]);

load F1,F2 from excel2.xls (biff, embedded labels, table is [Sheet1$]);

temp2:

LOAD F1, count(distinct F2) as totalLinesOfF2

resident temp group by F1;

temp3:

inner keep (temp)

LOAD F1, totalLinesOfF2, 1 as additional_char

resident temp2 where totalLinesOfF2=1;

drop table temp2;

F1 is/are the key/s. F2 is/are the fields which are not the unique key (other dimension, or just measures).

In the example we are loading the excels' data, grouping it to count the number of rows for each key, and then inner joining/keeping them with the lines which appear only once.

Regards,

Montal.

[boorgura]

you can use the where not exists function in this case.

for this you will need to know which field needs to be checked for uniqueness.

Table:

LOAD *, KEY from table1;

concatenate

LOAD *, KEY from table2 where not exists(KEY);

Here, KEY can be a concatenated or hash expression of multiple fields.

(For example, may be date and ID)

[Anonymous]

Thank you so much. it worked!

[boorgura]

And just a clarification,

I dont think what you want A-B.

Because, A-B will give all of what's in A excluding what's in B.

I dont think that's what you want.

Can you please confirm the same?