topic Re: QVW file compounds calculation with every reload in QlikView

QVW file compounds calculation with every reload

Anonymous — Mon, 22 Dec 2014 15:46:24 GMT

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:

Re: QVW file compounds calculation with every reload

Mon, 22 Dec 2014 15:55:02 GMT

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

Re: QVW file compounds calculation with every reload

Anonymous — Mon, 22 Dec 2014 16:01:10 GMT

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

Re: QVW file compounds calculation with every reload

Anonymous — Mon, 22 Dec 2014 16:03:17 GMT

Why are you loading QVD twice?

Are you trying to do incremental load?

Re: QVW file compounds calculation with every reload

prieper — Mon, 22 Dec 2014 16:07:39 GMT

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

Re: QVW file compounds calculation with every reload

Anonymous — Mon, 22 Dec 2014 19:45:10 GMT

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

Re: QVW file compounds calculation with every reload

Anonymous — Mon, 22 Dec 2014 20:21:10 GMT

NO REPLY???

Re: QVW file compounds calculation with every reload

prieper — Mon, 22 Dec 2014 20:46:40 GMT

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

Re: QVW file compounds calculation with every reload

Anonymous — Tue, 23 Dec 2014 13:37:20 GMT

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

Re: QVW file compounds calculation with every reload

Anonymous — Tue, 23 Dec 2014 13:39:32 GMT

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

Re: QVW file compounds calculation with every reload

Tue, 23 Dec 2014 14:03:57 GMT

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;

Re: QVW file compounds calculation with every reload

Anonymous — Tue, 23 Dec 2014 17:55:15 GMT

@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.

Re: QVW file compounds calculation with every reload

prieper — Tue, 23 Dec 2014 18:21:04 GMT

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