How to use startdate-1 as prev enddate?

[#1]

Hi

I have a tabel with income estimates over time by PersonID. Each income-estimate has  registerdate,  startdate, enddate and estimate-ID (uniq per registration). The problem are that the registrator can registrate new income-estimates without ending the previous. Registrations without a enddate gets date(today()). And that my problem, I need to close the prev registrations.

The registration has a specific ID in desc order so I´m hopeful someone here can help me how to set enddates were there are a new registration. I Think it should be possible to use the new startdate - 1 as enddate for the prev registrationId by each PersonID.

Attached are a sample data with some Persons that have multiple, open registrations. And two that are correct registrated.

[datanibbler]

Hi Hofstedt,

in the script, that's easy.

You have to sort your table

- in asc. order  by person_ID (so all records with the same ID are in one bunch, so to speak)

- in desc. order by date (so the newer date is in front of the older)

=> Then, in another LOAD (might be doable in one, but I like to keep things clear by doing it step-by-step),

      use the PREVIOUS() function to calculate, like > StartDate - 1 < (that would  be the start_date of the newer

      period minus one day) and generate an end_date like this.

HTH

Best regards,

DataNibbler

[MK_QSL]

Test:

LOAD

  Date(Date#(FOMDAT,'YYYY-MM-DD')) as FOMDAT,

  Date(Date#(RegisterDate,'YYYY-MM-DD')) as RegisterDate,

    EstimateID,

    IF(Date(Date#(TOMDAT,'YYYY-MM-DD')) = Date(Today()), Null(), Date(Date#(TOMDAT,'YYYY-MM-DD'))) as TOMDAT,

    PersonID

FROM

Tmp5.qvd

(qvd);

NoConcatenate

Final:

Load

  FOMDAT,

  TOMDAT,

  EstimateID,

  RegisterDate,

  IF(PersonID = Peek(PersonID) and IsNull(TOMDAT), Previous(RegisterDate), TOMDAT) as TOMDATNEW,

  PersonID

Resident Test

Order By PersonID, RegisterDate Desc;

Drop Table Test;

[MK_QSL · Accepted solution]

Little change to previous reply....

Test:

LOAD

  Date(Date#(FOMDAT,'YYYY-MM-DD')) as FOMDAT,

  Date(Date#(RegisterDate,'YYYY-MM-DD')) as RegisterDate,

    EstimateID,

    IF(Date(Date#(TOMDAT,'YYYY-MM-DD')) = Date(Today()), Null(), Date(Date#(TOMDAT,'YYYY-MM-DD'))) as TOMDAT,

    PersonID

FROM

Tmp5.qvd

(qvd);

NoConcatenate

Final:

Load *,

  Date(IF(ISNULL(TOMDATNEW),Today(),TOMDATNEW)) as TOMDATNEW2;

Load

  FOMDAT,

  TOMDAT,

  EstimateID,

  RegisterDate,

  IF(PersonID = Peek(PersonID) and IsNull(TOMDAT), Previous(RegisterDate), TOMDAT) as TOMDATNEW,

  PersonID

Resident Test

Order By PersonID, RegisterDate Desc;

Drop Table Test;

======================

TOMDATNEW2 is the field you want...

[MarcoWedel]

please post desired output table also.

thanks

regards

Marco

[MK_QSL]

Here I have considered NULL value wherever TOMDAT is today's date....

And replaced it with RegisterDate...

If you want to replace it with FOMDAT....

use as below

IF(PersonID = Peek(PersonID) and IsNull(TOMDAT), Previous(FOMDAT), TOMDAT) as TOMDATNEW,

rest all same...