topic A - B Load in QlikView

A - B Load

Anonymous — Thu, 16 Dec 2010 13:53:21 GMT

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

A - B Load

Thu, 16 Dec 2010 15:10:45 GMT

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.

A - B Load

boorgura — Thu, 16 Dec 2010 15:59:48 GMT

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)

A - B Load

Anonymous — Thu, 16 Dec 2010 16:05:58 GMT

Thank you so much. it worked!

A - B Load

boorgura — Thu, 16 Dec 2010 16:06:03 GMT

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?