Can not Get Incremental Load to work

[Anonymous]

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

[SunilChauhan]

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.

[#3]

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

[SunilChauhan]

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

so i dont thing its possible without any unique key

[its_anandrjs]

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

[SunilChauhan]

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

[#7]

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.

[SunilChauhan]

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

[Anonymous]

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

[Anonymous]

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.