topic Re: only want to load new records only... in QlikView

only want to load new records only...

Fri, 21 Mar 2014 07:26:24 GMT

I am using incremental load scenario and I have the data like this:-

I have receive the file on 21-Mar with data:-

field1 field2 field3 field4

1 a a1 2

1 b b1 2

1 b b2 4

1 c c1 3

and I have the file receive on 22-Mar with data:-

field1 field2 field3 field4

1 a a1 3

1 b b2 3

1 d d1 4

and field1,field2 and field3 all three are making a primary key(field1,field2,filed3) and my requirement is that when I load 22-Mar file then It only pick the record receive on 22-Mar only not on 21-mar file for filed1 with value1.But it is picking the records as shown below:-

field1 field2 field3 field4

1 a a1 3

1 b b1 2

1 b b2 3

1 c c1 3

1 d d1 4

and I am using the script shown below:-

load field1,field2,field3,field4 from 22-Mar file;

load field1,field2,field3,field4 from 21-Mar file

where not exist primary key(field1,field2,field3);

So, Please help me on this ....

I want to show only these records as shown below after incremental load:-

field1 field2 field3 field4

1 a a1 3

1 b b2 3

1 d d1 4

Re: only want to load new records only...

its_anandrjs — Fri, 21 Mar 2014 07:40:44 GMT

Hi,

Use where exists( ) function for this.

Hope this helps

Thanks & Regards

Re: only want to load new records only...

its_anandrjs — Fri, 21 Mar 2014 08:02:33 GMT

Hi,

I suggest you have to use Max(Date) function for loading incremental.

Hope this helps

Thanks & Regards

Re: only want to load new records only...

Fri, 21 Mar 2014 08:42:26 GMT

OK fine Anand but I also want to load the data which is store in previous qvd made on 21-Mar but not in file received on 22-Mar.for exp:- I also have the date in 21-Mar file like shown below:-

field1 field2 field3 field4

2 a a1 5

2 a a2 4

This data is in 21-Mar file but not in 22-Mar file but I wants that if value of filed1 is repeated again in next file received then I only wants to have data receive in next file not from the previous file and the data which not receive in next file but it exist in previous file then that data should also be loaded in app.

Re: only want to load new records only...

Fri, 21 Mar 2014 08:44:27 GMT

Thanks for reply.

Actually don't have any Date field and I can't use Max(date) as per my requirement.

Re: only want to load new records only...

Anonymous — Fri, 21 Mar 2014 08:56:19 GMT

Check this out

Tab1:

load * Inline

[

field1 , field2 , field3 , field4

1 , a , a1 , 2

1 , b , b1 , 2

1 , b , b2 , 4

1 , c , c1 , 3

];

NoConcatenate

Tab2:

load * Inline

[

field1 , field2 , field3 , field4

1 , a , a1 , 3

1 , b , b2 , 3

1 , d , d1 , 4

];

NoConcatenate

Final:

LOAD *,field1 & field2 & field3 as Key Resident Tab2

;

outer join

LOAD *,field1 & field2 & field3 as Key Resident Tab1

where not Exists(Key,field1 & field2 & field3 )

;

DROP Table Tab1,Tab2;

Re: only want to load new records only...

preminqlik — Fri, 21 Mar 2014 09:00:54 GMT

Load

field1,

field2,

firstsortedvalue(field3,-Date) as field3 ,

firstsortedvalue(field4,-Date)

field4

firstsortedvalue(Date,-Date) as Date

group by field1,field2;

Load *,

date(Date#(trim(filebasename()),'DD-MMM-YYYY'),'DD/MM/YYYY') as Date

from

$(vPath)/22-Mar-2014.xls;

Re: only want to load new records only...

Anonymous — Fri, 21 Mar 2014 09:02:26 GMT

Tab2: will be new file and Tab1 will be the old one.........

Regards

Nitin

Re: only want to load new records only...

Fri, 21 Mar 2014 09:03:55 GMT

Thanks for reply nitin sir but it is also loading the data which is in Tab1 but I onlty wants to see tab 2 data means I want to replace the tab1 data with tab2 data if it exist in tab2 else just load it from tab1.

Re: only want to load new records only...

Fri, 21 Mar 2014 09:07:34 GMT

for exp:- If field1 with value 1 is there in tab1 and tab2 then I only want to have data in tab2 and if tab1 have data in field1 with value 2 and it is not in tab2 then also load the filed1 with 2 value.

Re: only want to load new records only...

Anonymous — Fri, 21 Mar 2014 09:07:51 GMT

Yes its replacing data of tab1 if available in Tab2, otherwise

Loads from Tab1 only...

Example:

Tab1:

Field 1 Field2 Field 3 Field 4

1 a a1 2

Tab2:

Field 1 Field2 Field 3 Field 4

1 a a1 3

Output is having

Field 1 Field2 Field 3 Field 4

1 a a1 3

otherwise previous data from Tab1 is loaded

Re: only want to load new records only...

Fri, 21 Mar 2014 09:13:15 GMT

yes sir it is replacing but also loading data from tab1 like shown below:-

field1 , field2 , field3 , field4

1 , b , b1 , 2

1 , c , c1 , 3

these entries are not there in tab2 so it should not come.

Actually Field1,Filed2 and Field 3 makes Primary Key but It will only watch for Field1 and If it repeated then replace all value of tab1 with tab2.

Re: only want to load new records only...

preminqlik — Fri, 21 Mar 2014 09:26:56 GMT

have you tried the above solution ?

Re: only want to load new records only...

Fri, 21 Mar 2014 09:36:50 GMT

Thanks for the reply but why you are not loading 21-March file.

Re: only want to load new records only...

preminqlik — Fri, 21 Mar 2014 09:49:02 GMT

Oh for your understanding i have sent like that, keep all the files in one folder and correct the path , please put * on loading as given below

Load

field1,

field2,

firstsortedvalue(field3,-Date) as field3 ,

firstsortedvalue(field4,-Date)

field4,

firstsortedvalue(Date,-Date) as Date

group by field1,field2;

Load *,

date(Date#(trim(filebasename()),'DD-MMM-YYYY'),'DD/MM/YYYY') as Date

from

$(vPath)/*.xls;

Re: only want to load new records only...

Fri, 21 Mar 2014 10:18:50 GMT

Thanks for the reply prem. But It is just concatenating both files.

It is giving o/p like shown below:-

Re: only want to load new records only...

kedar_dandekar — Fri, 21 Mar 2014 11:17:42 GMT

Hi Anand,

As I understand from your description you want to

- use old data from [Mar21] where field1 does not match with [Mar22].field1 data , and concatenate it with

- new data from [Mar22] file

Please try the following option.. and let me know if it works ..

//Mar22 data
[Mar22]:
LOAD * INLINE [
    field1, field2, field3, field4
    1, a, a1, 3
    1, b, b2, 3
    1, d, d1, 4
];


NoConcatenate
//Get unique Field1 values from Mar22 table for using it in the not-exists load
[Mar22_field1]:
LOAD Distinct field1 as Mar22_f1 Resident Mar22;


NoConcatenate
//Mar21 data
[Mar21]:
LOAD * INLINE [
    field1, field2, field3, field4
    1, a, a1, 2
    1, b, b1, 2
    1, b, b2, 4
    1, c, c1, 3
    2, a, a1, 5
    2, a, a2, 4
];


NoConcatenate
[Final]:
LOAD * Resident [Mar22]; //load new data
LOAD * Resident [Mar21] where not Exists(Mar22_f1, field1); //load old data where field1 does not match with the new data


DROP Table Mar21, Mar22, [Mar22_field1];

Re: only want to load new records only...

Fri, 21 Mar 2014 11:34:47 GMT

I found the solution:--

Solution is:-

load distinct field1,1 as status form tab2;

join

load * from tab1;

store A into A.qvd;

drop A;

load * from tab2;

load * from A.qvd where status<>1;