topic sum result is showing double then sql result. in QlikView

sum result is showing double then sql result.

riishabhz — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: sum result is showing double then sql result.

dplr-rn — Tue, 10 Sep 2019 14:42:14 GMT

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

Re: sum result is showing double then sql result.

riishabhz — Tue, 10 Sep 2019 15:38:28 GMT

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

Re: sum result is showing double then sql result.

dplr-rn — Tue, 10 Sep 2019 15:39:55 GMT

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

Re: sum result is showing double then sql result.

riishabhz — Tue, 10 Sep 2019 15:53:28 GMT

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

Re: sum result is showing double then sql result.

chriscammers — Tue, 10 Sep 2019 16:08:56 GMT

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

Re: sum result is showing double then sql result.

riishabhz — Tue, 10 Sep 2019 16:12:28 GMT

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

Re: sum result is showing double then sql result.

chriscammers — Tue, 10 Sep 2019 19:08:57 GMT

Storing a qvd will automatically overwrite the file.

Re: sum result is showing double then sql result.

Brett_Bleess — Mon, 23 Sep 2019 15:56:15 GMT

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