Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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;
@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.
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