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.
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
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)
