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]

what are the fiedls u have in xl ?

after the store command put  drop table carsle;

and change xl table name as carsle1  instead of carsle

[Anonymous · Accepted solution]

Hi,

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;

Regards

Neetha

[Anonymous]

Why are you loading QVD twice?

Are you trying to do incremental load?

[prieper]

This is exactly the behaviour expected from this script:

• Carsale: Load 1 set of data from qvd;
• Carsale: Load another set of data from Excel; // this will be automatically appended to the first table, if the fieldnames are same
• Carsale: Append all data from qvd and append to the last table;  // might be another set of values, as not specified to which table it should append
• Store the table Carsale into qvd

Not sure, what the purpose of your script is, usually it might be

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;

n.b.:

It makes always sense to reference properly, i.e. give proper table-names for each individual table and also specify, to which table you wish to concatenate or join.

HTH Peter

[Anonymous]

Thank you everyone for the help.

Neetha_p and @ Peter Rieper, your instructions i followed. But still my total (qty sold) is way more than the excel file. Please comment. Incremental load should only add the "new increase" but it is adding at an exponential rate. I have attached my script and excel file below.

Table1:

LOAD *

FROM

carsale_2.QVD

(qvd);

Concatenate(Table1)

load*

FROM

[Incre Example.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Date > 4/9/2013;

store Table1 into carsale_2.QVD

[Anonymous]

NO REPLY???

[prieper]

So what do you expect?

You load the qvd, add all data after 4/9, store combined data into qvd.

When reloading the same repeats:

You load the qvd (but then incl the data from Excel, as stored above), add the Excel with all data after 4/9 and store again.

As advised earlier, you may have a look at the EXIST-condition (limiting by a keyfield) or you may store the last update-date and include in a conditional clause then

HTH Peter

[Anonymous]

I need help with this. What I am trying to do is peform an incremental load- I only want to add the new row, which is sales made on 4/9/2013, when 6 grand prixs were sold. I have the script below. Its adding every thing twice. Can some one advise on how you can add only the new data. The snaps below are for a straight table where the Expn is the sum of all qty sold. As from the excel file, I want the total grand prixs to be only 21 but it is showing 42, which is double the amount.

Table2:

LOAD *

FROM

[Incre Example.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Date >= 4/9/2013;

Concatenate

LOAD *

FROM

Incremental_1.QVD

(qvd);

store Table2 into Incremental_2.QVD(qvd);

[Anonymous]

I have saved "old data" sales before 4/9/2013 on "Incremental_1.QVD" which you can see being the data source in the script. I thought I understood Incremental load but not so