Compare dates within same table to set flag

[quilldew]

Hi,

I have a table that contains records of referrals, with a key [Key ClientReferral] which links to a client table which also contains a [Key Client].

The referral table contains two dates for each [Key ClientReferral], a [ReferralReceivedDate] and [ReferralDischargeDate], and I am trying to find a way to create a flag based on whether a referral for a particular [Key Client] has taken place within 30 days of referral discharge.

I have no idea how to go about it seeing as the dates are stored within the same table but will be on different [Key ClientReferral] records and I'll need to compare them based on a key in another table.

I'm confusing myself, let me know if you need an example.

[ahaahaaha]

Hi Daemon,

Have you similar structure of data?

Regards,

Andrey

[quilldew]

Hi Andrey,

The setup is a bit backwards (I didn't create the table structure).

Taking your example, there would be no [Key Client] in Table1 and there would be a [Key ClientReferral] in Table2.

If I have to place the [Key Client] in Table1 I can do though.

[quilldew]

Can we bump our own threads?

[jonathandienst]

>>whether a referral for a particular [Key Client] has taken place within 30 days of referral discharge

Are you looking to compare referral dates between a new referrals and a previous discharge?

[quilldew]

Hi Jonathan,

If I was creating this in Excel I would order the table by [Key Client], ReferralReceivedDate then ReferralDischargeDate and then check each record to the previous record to see if a) the [Key Client] is the same and if so b) the ReferralReceivedDate is within 30 days of the ReferralDischargeDate on the previous record. I would then set a flag.

I only need to count the days between to know if they are within 30 days of each other, I won't need to store the amount of days.

Hope this helps.

[jonathandienst · Accepted solution]

Here's some rough code for that:

LOAD [Key Client Referral],

  If(ReferralInterval < 30, 1, 0) as IntervalFlag

;

LOAD [Key Client Referral],

  If ([Key Client] = Previous([Key Client]),

  ReferralReceivedDate - Previous(ReferralDischargeDate),

  100

  ) as ReferralInterval

Resident  <enter name of table here>

ORDER BY [Key Client],

  ReferralRecivedDate

;

I am assuming that the dates are normal QV numeric date values.

[jonathandienst]

And if Key Client is not in the same resident table as the referral and the dates, then you can use a mapping like this:

MAP_CLIENTS:

Mapping LOAD [Key Client Referral], [Key Client] Resident ...;

LOAD [Key Client Referral],

  If(ReferralInterval < 30, 1, 0) as IntervalFlag

;

LOAD [Key Client Referral],

  If (ApplyMap(MAP_CLIENTS, [Key Client Referral]) = ApplyMap(MAP_CLIENTS, Previous([Key Client Referral])),

  ReferralRecivedDate - Previous(ReferralDischargeDate),

  100

  ) as ReferralInterval

Resident  ...

ORDER BY ApplyMap(MAP_CLIENTS, [Key Client Referral])

  ReferralRecivedDate

;

[quilldew]

Okay,

The first code runs, I haven't checked the results yet but it at least runs after I have left joined my [KEY Client] into the table with the referral dates.

I took the join out for the second piece of code, while trying to work out how it works, but I'm receiving an error as Field not Found but it won't tell me which Field

Field not found

LOAD [KEY ClientReferral],

  If (ApplyMap(MAP_CLIENTS, [KEY ClientReferral]) = ApplyMap(MAP_CLIENTS, Previous([KEY ClientReferral])),

  ReferralReceivedDate - Previous(ReferralDischargeDate),

  100

  ) as ReferralInterval

Resident  tbl_Referrals

ORDER BY ApplyMap(MAP_CLIENTS, [Key ClientReferral])ReferralReceivedDate