topic Re: Incremental issue. in QlikView

Incremental issue.

Thu, 25 Jun 2015 17:42:43 GMT

Hi Team,

I am new to the incremental funtionality in qlikview. Thus stuck in resolving following issue:

I have a table with 20 unique records.

Example: Table 1

Step 1:

Product Prdname Price Updated

101 A 10 1/06/2015

102 B 20 2/06/2015

And So on.. Uptil,

120 T 200 20/06/2015

Step 2: After loading this 20 records I need to perform compare operation for 10 new records. Among these 10 records 4 are same records and remaining are 3 are new and 3 are old records with new dates (3+3=6).

Table 2: 10 records (4 same records, 6 new)

Product Prdname Price Updated

101 A 10 1/06/2015

102 B 20 2/06/2015

121 U 210 21/06/2015

122 V 220 22/06/2015

123 W 230 23/06/2015

Step 3: Now I need to delete old records with new dates from 20 records.(17 records remains)

Step 4: Now I will concatenate all the 6 records into 20 record table. Thus the final table should hold 23 records.

Kindly help me generate the above output with respective script.

Thanks in advance !

Re: Incremental issue.

ramoncova06 — Thu, 25 Jun 2015 18:57:39 GMT

it would be easier creating a composite key and then working out of that, the asumption here is that the product prdname is your key

OldRecords

load

Product,

Prdname,

Price ,

Updated,

Product&'_'&Prdname as %K_ProdKey_Old

from your source

;

NewRecords

load

Product,

Prdname,

Price ,

Updated,

Product&'_'&Prdname as %K_ProdKey

from your source

;

concatenate(NewRecords)

load

Product,

Prdname,

Price ,

Updated,

%K_ProdKey_Old as %K_ProdKey

resident OldRecords

where not exists(%K_ProdKey,%K_ProdKey_Old);

drop table OldRecords;

Re: Incremental issue.

Fri, 26 Jun 2015 00:59:16 GMT

Hi Ramon,

Thanks for your inputs. Once I run the above script, I will get back to you.

Re: Incremental issue.

Fri, 26 Jun 2015 05:11:52 GMT

Hi Ramon,

I tried implementing as per your suggestions. But in the final table I get only 13 records where I should get 23 records.

Please find the below excel files for reference.

Your kind help would be appreciated.

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
103	Fruit	30$	3/3/2015
104	Jeans	50$	4/4/2015
105	Pen	12$	20/5/2015	Updated
106	Wallet	25$	26/6/2015
107	Shoes	70$	27/7/2015
108	Flowers	35$	25/6/2015	New
109	Jewellery	425$	26/6/2015
110	Key Chains	17$	27/6/2015

Thanks,

Robin

Re: Incremental issue.

Fri, 26 Jun 2015 05:26:09 GMT

I tried implementing it again and it works. Thanks again

Re: Incremental issue.

Fri, 26 Jun 2015 05:48:07 GMT

For simplicity of the code, divide your extraction process into two files

1. Full Load Extract Script.QVW

2. Incremental Load Extract Script.QVW

In Full load extract script, load all the required data from your source into a QVD. Something like below

Table1:

SQL Select * from table;

Store Table1 into Table1.QVD (qvd);

Drop table Table1;

This file has to be reloaded for the first extraction or whenever all the data needs to be extracted from the table for whatever reason.

To do Insert and Update incremental extract, the data should have a column which identifies the time a record has been inserted or updated (In your scenario, it is Updated). And a primary key (Assuming Product primary key in the scenario).

Incremental Load Extract Script should have something like below. This should be reloaded as per the data refresh frequency required.

Max_Date_Previous_Load:

Load Max(Updated) as MaxDatePevLoad

From Table1.QVD (qvd);

Let vMaxDatePrevLoad = Peek('MaxDatePreLoad'); //Putting the Max Date value into a variable

DeltaLoad:

SQL Select * from table Where Updated > $(vMaxDatePrevLoad);

OLD_EXCLUDING_UPDATED:

Concatenate(DeltaLoad) //Concatenating this to DeltaLoad table

Load * From Table1.QVD (qvd)

Where not Exists(Product); //Not loading the Products from QVD which are available in the DeltaLoad

Store DeltaLoad into Table1.QVD (qvd); //Replacing the old QVD

Drop table DeltaLoad;

Re: Incremental issue.

Fri, 26 Jun 2015 07:15:07 GMT

Hi Kranthikumar,

Thanks a lot for the brief.

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