topic Re: Load only unique field records from second source file in QlikView

Load only unique field records from second source file

Mon, 21 Jul 2014 17:21:34 GMT

Hi all,

I have two source data files that I am loading. The first will be the "master" file (File A), and I want all data records. The second file (File B) has some duplicate info, so I want to selectively load from this file.

What I want to do is first load all data from File A. There will be a field called "CheckNumber" which I will use as the condition reference for File B. When loading from File B, if the CheckNumber field in matches an already existing CheckNumber from File A, I do not want to load it. I want only new/unique CheckNumbers to be loaded from File B. Is there a way to do this?

At first I thought incremental load might solve my problem, but I think that only helps if there is data already loaded into Qlikview and you want to pick up only new line items. This will not help me because the full line item associated with a CheckNumber from File A might be different than that of File B due to the time at which the report was created. I specifically want to condition the load on the CheckNumber field. Below is an example:

File A:

//Load all data here

CheckNumber, SomeData

1 123

2 234

3 456

File B:

//Only load CheckNumber 5 and 6

CheckNumber, SomeData

2 444

3 777

5 27

6 45

Re: Load only unique field records from second source file

avinashelite — Mon, 21 Jul 2014 17:29:00 GMT

Hi Paul,

Use not Exist function like below:

load checknumber,

data ;

from file A

load checknumber,

data

from file B where not Exist(checknumber);

Re: Load only unique field records from second source file

Mon, 21 Jul 2014 18:08:09 GMT

But doesn't this only load line items where CheckNumber is Null? I don't want that. I want it to load only if the CheckNumber is a new string.

Re: Load only unique field records from second source file

maxgro — Mon, 21 Jul 2014 19:10:41 GMT

I think Avinash answer is correct:

it loads records from file b with a new check number (checknumber not already loaded)

FileA:

//Load all data here

load * inline [

CheckNumber, SomeData

1 , 123

2 , 234

3 , 456

];

Concatenate (FileA)

//Only load CheckNumber 5 and 6

load * inline [

CheckNumber, SomeData

2 , 444

3 , 777

5 , 27

6 , 45

6 , 4500

]

Where Not Exists (CheckNumber);

Re: Load only unique field records from second source file

avinashelite — Mon, 21 Jul 2014 19:21:29 GMT

Hi Paul,

It will not load NULL values it will load only the new records, please try with the above format and let us now if you face any issue.

Re: Load only unique field records from second source file

Mon, 21 Jul 2014 19:31:01 GMT

This is great, except for the case of duplicates in the concatenation load (from File B). I tested the above script, and CheckNumber 6 will only load the record with SomeData = 45. Since there is already a record with the same CheckNumber, the script will not load the line item with SomeData 4500. I still need CheckNumbers that are duplicated within the same table (File B), so long as the CheckNumber doesn't exist in File A.

Re: Load only unique field records from second source file

maxgro — Mon, 21 Jul 2014 19:39:13 GMT

if you want duplicate from b, try this (in bold the difference with previous script)

FileA:

//Load all data here

load * inline [

CheckNumber, SomeData

1 , 123

2 , 234

3 , 456

];

tmp:

load distinct CheckNumber as chk Resident FileA;

Concatenate (FileA)

//Only load CheckNumber 5 and 6

load * inline [

CheckNumber, SomeData

2 , 444

3 , 777

5 , 27

6 , 45

6 , 4500

]

Where Not Exists (chk, CheckNumber);

drop table tmp;

Re: Load only unique field records from second source file

avinashelite — Mon, 21 Jul 2014 19:46:40 GMT

Hi Paul,

try like below to load the duplicate values too:

load checknumber as checknumber1,

data ;

from file A

concatenate

load checknumber,

data

from file B where not Exist(checknumber1,checknumber);

refer this

Understanding EXISTS() function - Qlikview vs SQL/RDBMS