Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use startdate-1 as prev enddate?

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.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

5 Replies
datanibbler
Champion
Champion

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

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

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

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