Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
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
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;
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...
please post desired output table also.
thanks
regards
Marco
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...