topic Re: Re: Duplicate summation in QlikView

Duplicate summation

Tue, 29 Apr 2014 13:59:29 GMT

Hi,

I was wondering: is there a way to avoid duplicate summation of an amount? Right now I am loading in information from different days through incremental loads, however some accounts carry over from day-to-day. For Example, say invoice number 100 is in the data load for yesterday and today. The invoice was for $100. Right now the summation formula adds the two, even though they are the same, for a total of $200. Is there a way to make Qlikview recognize they are the same and show a total of only $100?

Re: Duplicate summation

MK_QSL — Tue, 29 Apr 2014 14:01:31 GMT

If you are using incremental load, how invoice numbers are getting duplicated ? !!

Re: Duplicate summation

its_anandrjs — Tue, 29 Apr 2014 14:08:30 GMT

Use last updated date in the database and load only updated record and if having a primary key then use that also.

Re: Duplicate summation

jonathandienst — Tue, 29 Apr 2014 14:17:02 GMT

...or you can use

Where Not(Exists(InvoiceNo))

as a condition in your incremental load.

But date as per anand's post ids better is the dataset is SQL and large as you can run that date filter in the database query.

HTH

Jonathan

Re: Duplicate summation

maxgro — Tue, 29 Apr 2014 14:46:56 GMT

do you need historical data?

no: you can use previous answers to eliminate duplicates (invoice) in your incremental load

yes: you can flag (load) the last accounts and in chart expression filter using that flag

Re: Duplicate summation

Wed, 30 Apr 2014 21:11:11 GMT

yes i do need historical data. i also realized I wasn't really doing an incremental load. however, I do not know if an incremental load is applicable as that would only show new accounts and would not show accounts that had been closed since the last load.

Re: Duplicate summation

Wed, 30 Apr 2014 21:20:31 GMT

in other words is there a way for me to have an item connected to two Dates since it has carried over, yet only get counted as one account. AKA Invoice number 100 would show up for 4/28/2014 AND 4/29/2014 but only be one invoice at $100 instead of 2

Re: Duplicate summation

maxgro — Wed, 30 Apr 2014 21:30:36 GMT

see attachment

Re: Re: Duplicate summation

maxgro — Wed, 30 Apr 2014 21:34:41 GMT

another try, this with set analysis

Re: Duplicate summation

Thu, 01 May 2014 14:47:02 GMT

Thanks! This is definitely a step in the right direction. However, another problem I realized is that for some reason some items have the same invoice number, so if there were two invoices on April 30th with the same number only one is getting flagged. Is there a way around this to flag both?

Thank you for all the help.

Re: Duplicate summation

PrashantSangle — Thu, 01 May 2014 14:50:23 GMT

Hi,

Try with aggr()

Aggr(Sum(filed),Invoice Number,DateField)

Regards,

Re: Duplicate summation

Thu, 01 May 2014 14:57:32 GMT

Where would I fit that into the code? Sorry I'm not much of a developer, I just somehow got placed in a job where I have to set up a dashboard. Right now my code for the flag is:

table:

NoConcatenate load

if(peek([Document Number])=[Document Number],0,1) as flaglast,

Resident Table_

order by [Document Number], [As Of] desc;

//DROP Table [Table_];

Re: Duplicate summation

PrashantSangle — Thu, 01 May 2014 15:01:19 GMT

Hi,

Use this in your front end.

Like When you create a Straight table.

There in expression tab you can use above code.

Regards,

Re: Re: Duplicate summation

maxgro — Thu, 01 May 2014 15:08:34 GMT

change the script (hope to understand)

source:
load *, rowno() as id;
load * inline [
invoice, val, date
1, 100, 01/01/2014
1, 110, 02/01/2014
1, 120, 03/01/2014
1, 130, 04/01/2014
1, 140, 05/01/2014
2, 200, 01/01/2014
2, 210, 02/01/2014
2, 220, 03/01/2014
3, 300, 04/01/2014
4, 400, 30/04/2014
4, 400, 30/04/2014
4, 400, 05/01/2014
];

left join (source)
load
  invoice,
  max(date) as date,
  1 as flaglast
Resident source
group by invoice;

Re: Duplicate summation

Thu, 01 May 2014 16:05:44 GMT

perfect. thank you!