sum result is showing double then sql result.

[riishabhz]

i have a incremental table , script is :-

max1:
LOAD max (SalesDate) as SalesDate
FROM
[..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd](qvd);

let date2=Date(peek('SalesDate',0,'max'));

drop table max1;

final1:
LOAD NCode,
PartnerCode,
Channel,
ETPCode,
SalesDate,
"Doc_Type",
BillNo,
CustomerNumber,
Season,
Style,
Color,
Size,
InvoiceQuantity,
ETPMRP,
RealisedSale,
TAXAmount,
TAXRate,
NETSale,
SalesPerson;
SQL SELECT *
FROM ETPEASV55.dbo."VWCashOrderTRN_Qlik" where SalesDate>'$(date2)';

Concatenate(final1)
LOAD NCode,
PartnerCode,
Channel,
ETPCode,
SalesDate,
Doc_Type,
BillNo,
CustomerNumber,
Season,
Style,
Color,
Size,
InvoiceQuantity,
ETPMRP,
RealisedSale,
TAXAmount,
TAXRate,
NETSale,
SalesPerson
FROM
[..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd]
(qvd);

now i have done table chart in which i want to do sum(realisedsale) on the basis of ncodes

the result of sum(realisedsale) is 10000

but when i verify sum(realisedsale) in my sql table its showing 5000

so qlikview is showing exactly double data.

also double data is in every column of qlikview like sum(invoicequantity) , etc

please any help.

[dplr-rn]

I would hazard a guess that the > condition is not working in the sql
check below statement . shouldnt the table name be max1?
let date2=Date(peek('SalesDate',0,'max'));

[riishabhz]

Sir sql is working fine, its just double of sql value in my qlikview.
Any solutions sir

[dplr-rn]

you didnt read my reply correctly. the sql call within qlik is not working IMO

[riishabhz]

Sir i correct it on my qlik but still same problem, should i have to make
new qvd for incremental from beginning ?
Or should i do sum(realisedsales)/2 in order to get results but that wont
be a correct solution i guess

[chriscammers · Accepted solution]

YOUR PEEK has an error, I think the date you are getting is either null or zero.

You have the third parameter in PEEK as 'max' and it should be 'max1'

I also think you have to initialize your qvd again and I would recommend adding a condition to exclude records that are retrieved by the SQL Check out the pseudo-code example below.

MaxDate:
Load Max(RecordDate) as MaxDate
From [myqvd.qvd](qvd);

Let vMaxDate = Date(peek('MaxDate',0,'MaxDate'));

MyData:
Load
   RecordID,...;
SQL
Select * from Table Where RecordDate > '$(vMaxDate)';

Concatenate(MyData)
Load * from [myqvd.qvd](qvd)
//This where clause will prevent duplication
Where not exists(RecordID);

Store MyData into [myqvd.qvd](qvd);

[riishabhz]

Hi sir,
Ok sir but i have one doubt , if i write again last line of store qvd will
that not duplicate records?

[chriscammers]

Storing a qvd will automatically overwrite the file.

[Brett_Bleess]

Rishabh, did Chris' posts help you get what you needed in your project?  If so, please do not forget to circle back and give them credit for the help by using the Accept as Solution button on any posts that did help with the final solution.  If you did something else in the end, please consider posting what you did and then mark that as the solution.  If you are still working upon things, leave an update letting everyone know where things stand.

Regards,
Brett