Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

sum result is showing double then sql result.

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.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

 

View solution in original post

8 Replies
Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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

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

Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

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

 

View solution in original post

Highlighted
Creator
Creator

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

Storing a qvd will automatically overwrite the file.

Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.