Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I am new to incremental load, kindly help with the underline query resolution
Please find the below tables 1 and 2 with details :
Table 1: 20 unique records and
Table 2: 10 records (i.e. 4 records Old order Old date, 3 New record orders and 3 Old order with New dates)
Table 1:
Product ProdName Price Updated
101 Watch 150$ 1/1/2015
102 Ring 300$ 2/2/2015
103 Fruit 30$ 3/3/2015
104 Jeans 50$ 4/4/2015
105 Pen 12$ 5/5/2015
106 Wallet 25$ 6/6/2015
107 Shoes 70$ 7/7/2015
108 Tshirts 100$ 8/8/2015
109 Laptop 500$ 9/9/2015
110 Keyboard 350$ 10/10/2015
111 Furniture 400$ 11/11/2015
112 Socks 20$ 12/12/2015
113 Bags 85$ 1/13/2015
114 Belts 50$ 1/14/2015
115 Trousers 200$ 1/15/2015
116 Saree 220$ 1/16/2015
117 Books 100$ 1/17/2015
118 Bracelet 120$ 1/18/2015
119 Caps 30$ 1/19/2015
120 Footwears 160$ 1/20/2015
Table 2:
Product ProdName Price Updated
101 Watch 150$ 1/1/2015 Old
102 Ring 300$ 2/2/2015 Old
103 Fruit 30$ 3/3/2015 Old
104 Jeans 50$ 4/4/2015 Old
105 Pen 12$ 20/5/2015 Date Updated
106 Wallet 25$ 26/6/2015 Date Updated
107 Shoes 70$ 27/7/2015 Date Updated
121 Flowers 35$ 25/6/2015 New
122 Jewellery 425$ 26/6/2015 New
123 Key Chains 17$ 27/6/2015 New
Reading the 6 records, Using Where Not Exist will give 17 records (17 because, we need to delete the exisitng 3 old records from the 20 records)
At last, concatenating 6 records (i.e. 3 New record orders and 3 Old order with New dates) to the 17 records, resulting into 23 records
Kindly help me framing a script based on this, I'm stuck. Beacuse what Roman have suggested worked for Records (i.e. Composite key gave me unique records=23) and not for Products (i.e. Need to get record orders=23)
Thanks in advance
Try this one,
Newtest:
Load *
Resident Table2
;
Concatenate (Newtest)
Load
*
resident
Table1
where
not Exists(Product);
drop Table Table2, Table1;
Hi kSindhukannan,
Thanks for your guidance.I tried the below script as you suggested. But seems I missed on something, as its failing. Kindly help me with it
Table1:
LOAD Product,
ProdName,
Price,
Updated,
Product&'_'&ProdName as %K_ProdKey
FROM
(ooxml, embedded labels, table is [Table 1]);
Table2:
LOAD Product,
ProdName,
Price,
Updated,
%K_ProdKey_Old as %K_ProdKey
FROM
(ooxml, embedded labels, table is [Table 2]);
Newtest:
Load * Resident Table2;
Concatenate (Newtest)
Load * resident Table1
where
not Exists(Product);
drop Table Table2, Table1;
Thanks
Robin
Hi,
Try this script
Data:
LOAD
*
INLINE [
Product, ProdName, Price, Updated
101, Watch, 150$, 1/1/2015
102, Ring, 300$, 2/2/2015
103, Fruit, 30$, 3/3/2015
104, Jeans, 50$, 4/4/2015
105, Pen, 12$, 20/5/2015
106, Wallet, 25$, 26/6/2015
107, Shoes, 70$, 27/7/2015
121, Flowers, 35$, 25/6/2015
122, Jewellery, 425$, 26/6/2015
123, Key Chains, 17$, 27/6/2015];
Concatenate(Data)
LOAD
*
INLINE [
Product, ProdName, Price, Updated
101, Watch, 150$, 1/1/2015
102, Ring, 300$, 2/2/2015
103, Fruit, 30$, 3/3/2015
104, Jeans, 50$, 4/4/2015
105, Pen, 12$, 5/5/2015
106, Wallet, 25$, 6/6/2015
107, Shoes, 70$, 7/7/2015
108, Tshirts, 100$, 8/8/2015
109, Laptop, 500$, 9/9/2015
110, Keyboard, 350$, 10/10/2015
111, Furniture, 400$, 11/11/2015
112, Socks, 20$, 12/12/2015
113, Bags, 85$, 1/13/2015
114, Belts, 50$, 1/14/2015
115, Trousers, 200$, 1/15/2015
116, Saree, 220$, 1/16/2015
117, Books, 100$, 1/17/2015
118, Bracelet, 120$, 1/18/2015
119, Caps, 30$, 1/19/2015
120, Footwears, 160$, 1/20/2015]
WHERE NOT Exists(Product);
Regards,
Jagan.
Hi Jagan,
Thanks for your inputs. The resultant table gives 23 records.
But I concern is we have not carried out incremental load and taken out the max of date ?
does this would suffice ? I am confused
Hi,
For this scenario this is the best script, and it works well. If you got the answer please close this thread.
Regards,
Jagan.
Hi Jagan,
The above query won't work, when we have new records thus this below script would suffice
Old:
LOAD Product,
ProdName,
Price,
Date(Updated,'MM/DD/YYYY') as Updated
FROM
(ooxml, embedded labels, table is [Table 1])
;
FinalOld:
NoConcatenate
Load *
Resident Old
order by Updated desc;
Drop Table Old;
Let vMaxDate = peek('Updated', 0, 'Old');
New:
NoConcatenate
LOAD Product as Product1,
ProdName,
Price,
Date(Updated,'MM/DD/YYYY') as Updated
FROM
(ooxml, embedded labels, table is Sheet1)
Where Date(Updated,'MM/DD/YYYY') > '$(vMaxDate)';
TempOld:
NoConcatenate
Load *
Resident FinalOld
where not exists(Product1, Product)
;
Concatenate(TempOld)
Load Product1 as Product, ProdName, Price, Updated Resident New;
Drop tables FinalOld, New;
Hi,
I didn't get what you are doing to do? If possible attach some sample files so that it would be easier to give solution. The earlier script which I gave is working as per your requirement, it is loading all new rows, updated rows and also non updated rows.
Regards,
Jagan.