1 Replies - 366 Views - Last Post: 14 March 2018 - 09:07 AM

#1 tokei   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 15-December 11

How to set zero for non last record per unique column ?

Posted 14 March 2018 - 01:16 AM

Hi all,

I have problem to how to set zero for non-last record per unique column id

For example , see the situation below

DocumentDate	DocumentNo	InvoiceNo	BillToLeadID	CustomerLeadID	Amount	    InvoiceBalanceAmount
13/03/18	 INCH011968A	INCH011968A	LD342742A	LD062750A	1,600.00	500    -- set to zero --
13/03/18	 RCCH006039A	INCH011968A	LD342742A	LD062750A	-1,000.00	500    -- set to zero --
14/03/18	 RCCH006040A	INCH011968A	LD342742A	LD062750A	-100.00	        500
14/03/18	 INCH011969A	INCH011969A	LD342742A	LD005595A	31.40	        0
14/03/18	 RCCH006041A	INCH011969A	LD342742A	LD005595A	-31.40	        0


I want set zero if non-last record per unique Invoice No. I have highlight row which non-last record per unique InvoiceNo column

The expectation result is :

DocumentDate	DocumentNo	InvoiceNo	BillToLeadID	CustomerLeadID	Amount	InvoiceBalanceAmount
13/03/18	 INCH011968A	INCH011968A	LD342742A	LD062750A	1600.00	        0
13/03/18	 RCCH006039A	INCH011968A	LD342742A	LD062750A	-1000.00	0
14/03/18	 RCCH006040A	INCH011968A	LD342742A	LD062750A	-100.00	        500
14/03/18	 INCH011969A	INCH011969A	LD342742A	LD005595A	31.40	        0
14/03/18	 RCCH006041A	INCH011969A	LD342742A	LD005595A	-31.40	        0


Note : The second and last record for Invoice No = INCH011969A is zero because the calculation result.

How to do it for situation above ? Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: How to set zero for non last record per unique column ?

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: How to set zero for non last record per unique column ?

Posted 14 March 2018 - 09:07 AM

I think you need to be clearer with the question, but let's see if I've understood:

Look at all records for each InvoiceNo, ordered by DocumentDate
For each InvoiceNo, set all InvoiceBalanceAmount's to 0 other than the most recent one?

Would you set row 5's InvoiceBalanceAmount to 0, even though it is already 0?

But... your DocumentDate only shows the date element, not the time, so the ordering isn't something you could use, nor rely on, currently.



I think you might also clarify why you are doing this, it seems an odd requirement to wipe out/ reset such information. (Why is the information there if you are going to remove it?)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1