QVW file compounds calculation with every reload

[Anonymous]

Inventory expn has sum(qty sold) and every time I do a reload, the number keeps going up!!! Any suggestions..?

Carsale:

LOAD Date,

     Item#,

     [Item name],

     [Qty Sold],

     country,

     Department

FROM

"carsale.QVD"

(qvd);

Carsale:

load *

From

[Incre Example.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Date >= '4/5/2013';

Concatenate

LOAD*

     From carsale.QVD

(qvd);

STORE Carsale into "carsale.QVD";

here is my script above:

[#2]

Hi,

Try this

Carsale:

load *

From

[Incre Example.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Date >= '4/5/2013';

Concatenate

LOAD *

From carsale.QVD

(qvd);

STORE Carsale into carsale.QVD;

[Anonymous]

@Peter Rieper,

Carsale: LOAD * FROM Carsale.qvd;

CONCATENATE (Carsale) LOAD * FROM Carsale.xls;  // here you may limit the data with EXISTS() or the like

STORE Carsale INTO Carsale.qvd;

when you use concatenate, it will add rows and that is why sum function was showing crazy additions.

As neetha suggested,

Carsale1:

LOAD Date,

     Item#,

     [Item name],

     [Qty Sold],

     country,

     Department

FROM

"carsale.QVD"

(qvd);

Noconcatenate

Carsale:

load *

From

[Incre Example.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Date >= '4/5/2013';

Concatenate

LOAD*

     From carsale.QVD

(qvd);

STORE Carsale into "carsale.QVD";

Drop Table Carsale1;

if you drop the previous table after adding new rows to it in a new table, it adds only the new rows. Therefore, this is the correct script. Many Thanks.

[prieper]

John,

this is not correct.

For an incremental load you need to have an identifyer, which records are already loaded, and which not.

This criteria is ideally a keyfield. Alternatively you may extract the last update and hand it over into a variable.

Provided, your data are consistant you may try a solution like:

1st time load:

Carsale: LOAD * FROM Carsale.xls ....; STORE Carsale INTO Carsale.qvd;

Then all other loads may be done as fllws:

Carsale: LOAD * FROM Carsale.qvd;

MaxDate: LOAD MAX(Date) AS MaxDate RESIDENT Carsale;

LET vMaxDate = PEEK('MaxDate', 0, 'MaxDate');

DROP TABLE MaxDate;

CONCATENATE (Carsale) LOAD * FROM Carsale.xls ... WHERE Date > $(vMaxDate);

STORE Carsale INTO Carsale.qvd;

HTH Peter