Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
Hi,
Use where exists( ) function for this.
Hope this helps
Thanks & Regards
Hi,
I suggest you have to use Max(Date) function for loading incremental.
Hope this helps
Thanks & Regards
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.
Thanks for reply.
Actually don't have any Date field and I can't use Max(date) as per my requirement.
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;
Load
field1,
field2,
firstsortedvalue(field3,-Date) as field3 ,
firstsortedvalue(field4,-Date) | as | 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;
Tab2: will be new file and Tab1 will be the old one.........
Regards
Nitin
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.
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.