topic Re: IncrementaL Query in QlikView

IncrementaL Query

Fri, 26 Jun 2015 07:34:37 GMT

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

Re: IncrementaL Query

Fri, 26 Jun 2015 08:16:12 GMT

Try this one,

Newtest:

Load *

Resident Table2

;

Concatenate (Newtest)

Load

resident

Table1

where

not Exists(Product);

drop Table Table2, Table1;

Re: IncrementaL Query

Fri, 26 Jun 2015 08:34:41 GMT

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

Re: IncrementaL Query

jagan — Fri, 26 Jun 2015 09:17:45 GMT

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.

Re: IncrementaL Query

Fri, 26 Jun 2015 09:32:02 GMT

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

Re: IncrementaL Query

jagan — Fri, 26 Jun 2015 09:45:21 GMT

Hi,

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

Regards,

Jagan.

Re: IncrementaL Query

Fri, 26 Jun 2015 10:34:49 GMT

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;

Re: IncrementaL Query

jagan — Fri, 26 Jun 2015 10:40:48 GMT

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.