topic How to use startdate-1 as prev enddate? in QlikView

How to use startdate-1 as prev enddate?

Thu, 28 Aug 2014 12:03:58 GMT

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.

Re: How to use startdate-1 as prev enddate?

datanibbler — Thu, 28 Aug 2014 12:33:02 GMT

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

Re: How to use startdate-1 as prev enddate?

MK_QSL — Thu, 28 Aug 2014 12:34:21 GMT

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;

Re: How to use startdate-1 as prev enddate?

MK_QSL — Thu, 28 Aug 2014 13:03:34 GMT

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

Re: How to use startdate-1 as prev enddate?

MarcoWedel — Thu, 28 Aug 2014 13:04:45 GMT

please post desired output table also.

thanks

regards

Marco

Re: How to use startdate-1 as prev enddate?

MK_QSL — Thu, 28 Aug 2014 13:14:57 GMT

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