topic Re: Can not Get Incremental Load to work in QlikView

Can not Get Incremental Load to work

Anonymous — Fri, 26 Dec 2014 14:52:30 GMT

Hi all, I need some help understanding Incremental Load. I had posted this issue in another thread and had found the solution ( i thought so) This same script was working but it is not anymore.

What I am trying to do: Add only new rows from the data source, which is sales after 4/10/2013. I have transactions until 4/10/2013 in the table "Old Data" (which is the 1st tab in excel file). I have new transactions after 4/10/2013 in the table "New Data" (the 2nd tab....) this you will find in the excel file attached. To achieve the addition of new rows, this is my approach to the issue: I first stored "Old Data" in a qvd file named "[1st.QVD]" and saved it in the same folder where the qvws and excel files needed for this task reside. After that, I have the script below that explains my approach and hence understanding of incremental load to solving this problem by adding only new rows. However, my straight table that has an expn showing the total qty sold, is showing way more amount of cars sold than actual. I should be able to total the actual numbers with incremental load, should not I...? This same script was working before but its not anymore!! May be you will able to see my mistake.

Script:

Table1:

LOAD Date,

Item#,

[Item name],

[Qty Sold],

country,

Department

FROM

[1st.QVD]

(qvd);

NoConcatenate

Table2:

Load *

FROM

[Incre Example.xlsx]

(ooxml, embedded labels, table is [New Data]);

Concatenate

Load *

FROM

[1st.QVD]

(qvd);

store Table2 into [1st.QVD];

Drop Table Table1;

I just want to add the 2 new rows to the old table. but my straight table shows the total qty sold increasing exponentially as below w/each reload

Re: Can not Get Incremental Load to work

SunilChauhan — Fri, 26 Dec 2014 15:11:06 GMT

in this case you need to capture date in date and time format also

i mean time stamp format 4/11/2013 11:00:03 and 4/11/2013 11:00:10

or there should be unique id also

i dont thing without that its possible.

Re: Can not Get Incremental Load to work

Fri, 26 Dec 2014 15:12:26 GMT

Try:

Concatenate

Load *

FROM

[1st.QVD]

(qvd)

where not exists (Date,Item#,[Item name] ...etc. for the rest of the fields that could be unique or primary key);

Re: Can not Get Incremental Load to work

SunilChauhan — Fri, 26 Dec 2014 15:21:54 GMT

i thing except date everything should be same , as i saw data above.

so i dont thing its possible without any unique key

Re: Can not Get Incremental Load to work

its_anandrjs — Fri, 26 Dec 2014 15:28:37 GMT

Hi,

It seems you are not using correct date format in the table load like 4/10/2013 this is string you have to capture correct date format also create a variable for dynamically fetch the maximum date from the table load for more please see the complete script

//First Qvd Creation and then comment this script
Table1:
LOAD Date,
Item#,
[Item name],
[Qty Sold],
country,
Department
FROM
[Incre Example.xlsx]
(ooxml, embedded labels, table is [Old Data]);
store Table1 into [1st.QVD];

DROP Table Table1;

///////////////////////////

//Incremental Load Start
Table1:
LOAD * FROM [1st.QVD] (qvd);

MaxDateTab:
LOAD date( Max(Date) ) as MaxDate Resident Table1;

LET vMaxDate = Peek('MaxDate',0,'MaxDateTab');

DROP Table Table1;

NoConcatenate
Table2:
LOAD *
FROM
[Incre Example.xlsx]
(ooxml, embedded labels, table is [New Data])
where Date > '$(vMaxDate)'; //'4/10/2013';

Concatenate
LOAD * FROM [1st.QVD] (qvd);
store Table2 into [1st.QVD];

Regards

Anand

Re: Can not Get Incremental Load to work

SunilChauhan — Fri, 26 Dec 2014 15:32:35 GMT

Table1:

Load *,

Date&[Item#]&Country&department as key

FROM

[1st.QVD]

(qvd);

Concatenate(Table1)

Load *

from Incre example.xls

where not exists (key,Date&[Item#]&Country&department);

but it should not work when entry for same date,department,country ,Item# . so this could be a solution in case entries are different

Re: Can not Get Incremental Load to work

Fri, 26 Dec 2014 15:36:22 GMT

It depends, if John, place a summary of daily sales, per country, item then the following NOT EXISTS clause should work:

Concatenate

Load *

FROM

[1st.QVD]

(qvd)

where not exists (Date,Item#,country)

In his case, Qlikview is not being told to restrict the values loaded and concatenated, so every time he loads the document, more records duplicated for our eyes but not for Qlikview are being loaded and saved to the main QVD.

So , in summary the NOT EXISTS clause in the Concatenate should do the Job.

Re: Can not Get Incremental Load to work

SunilChauhan — Fri, 26 Dec 2014 15:42:05 GMT

what if next time same date,same item# and for same country. then it will not include if quantity is different and depart is different

so i wotld suggets to include all field in where exists

Table1:

Load *,

Date&[Item#]&[Item Name]&Country&department&Quantitysold as key

FROM

[1st.QVD]

(qvd);

Concatenate(Table1)

Load *

from Incre example.xls

where not exists (key,Date&[Item#]&[Item Name]&Country&department&Quantitysold);

Re: Can not Get Incremental Load to work

Anonymous — Fri, 26 Dec 2014 15:54:27 GMT

Hi All, Thank you for your responses. I will try the suggested and update right back.

Re: Can not Get Incremental Load to work

Anonymous — Fri, 26 Dec 2014 17:19:17 GMT

I have found some other solutions as well:- Mario's suggestion worked. Distinct also worked. Plus of course, Anands's worked perfectly. I have summarized the 3 solutions below in screen shot. I wanted to know if the other 2 solutions are in fact correct and can be used. My understanding is that different functions (syntax) have to be used depending on nature of fields, transaction table, business model of the company...etc..) to accurately use the Increm load functionality. This one is very basic. I just want to add new sales data regardless of whether it was sold before or not. For example, Porsche was already sold b4 4/11/2013, but Audi was not. I want to fetch both of them because it is a new sale and has to be added to reflect the Revenue. well here is the snap shot of what scripts worked: Please comment. Thank you again.

Re: Can not Get Incremental Load to work

Anonymous — Fri, 26 Dec 2014 17:36:13 GMT

Anand,

I am trying to understand the logic behind using variables and Peek function. What I understood from your script is,

1st create a table to identify/store max date from Table 1(which is our old data) and then create a variable using peek function to fetch max date, which instead will be used to compare with date pertaining to new data. so if the data is new, it will be concatenated with QVD data(which has the old data) and finally this updated data will overwrite the QVD data, that was in there. So creating new table 'maxdatetab', creating variable using Peek function is to make qlikview understand how to recognize new data based on date?, because where Date > 4/10/2013 was not a proper/legit syntax to do the job?

I am failrly new to QV so was trying to understand the scripting language. Thank you again. Any comments will be wonderful.

Re: Can not Get Incremental Load to work

Fri, 26 Dec 2014 18:53:50 GMT

Here is a good tutorial on that, and not using peek function by Shilpan Patel.

https://www.youtube.com/watch?v=UkKTmnKSNQo

Re: Can not Get Incremental Load to work

Anonymous — Fri, 26 Dec 2014 21:29:54 GMT

Yes that's right,maxdate variable is used to compare with date in excel sheet and if date is greater then maxdate.

records are extracted into qlikview inmemory table and concatenated to QVD data.

All new records and old data is stored in QVD again.

Re: Can not Get Incremental Load to work

its_anandrjs — Fri, 26 Dec 2014 22:24:50 GMT

Yes you are on right way and other way also for this i explain all the Incremental load script for Insert new records to the old data and it has parts

1. At least you have to create the QVD first.

2. Store the Max date into the variable by finding max date in the table load

LET vMaxDate = Peek('MaxDate',0,'MaxDateTab');

3. And then load latest date by the help of the Max Date variable

LOAD *

FROM

[Incre Example.xlsx]

(ooxml, embedded labels, table is [New Data])

Where Date > '$(vMaxDate)';

4. Concatenate one day back Max - 1 rows to the loaded table rows and store the qvd also for later use

Concatenate

LOAD * FROM [1st.QVD] (qvd);

Regards,

Anand