IncrementaL Query

[#1]

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

[#2]

Try this one,

Newtest:

  Load *

     Resident Table2

  ;

     Concatenate (Newtest)

  Load

  *

  resident

  Table1

  where

  not Exists(Product);

  drop Table Table2, Table1;

[#3]

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

[jagan]

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.

[#5]

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

[jagan]

Hi,

For this scenario this is the best script, and it works well.  If you got the answer please close this thread.

Regards,

Jagan.

[#7]

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;

[jagan]

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.