Solved: Re: Duplicate summation - Qlik Community

Report Inappropriate Content · ‎2014-04-29

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?

maxgro · ‎2014-05-01

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;

View solution in original post

MK_QSL · ‎2014-04-29

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

its_anandrjs · ‎2014-04-29

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

jonathandienst · ‎2014-04-29

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

maxgro · ‎2014-04-29

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

Report Inappropriate Content · ‎2014-04-30

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.

Report Inappropriate Content · ‎2014-04-30

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

maxgro · ‎2014-04-30

see attachment

maxgro · ‎2014-04-30

another try, this with set analysis

Report Inappropriate Content · ‎2014-05-01

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.