topic Re: Compare dates within same table to set flag in QlikView

Compare dates within same table to set flag

quilldew — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Compare dates within same table to set flag

ahaahaaha — Thu, 25 May 2017 11:11:07 GMT

Hi Daemon,

Have you similar structure of data?

Regards,

Andrey

Re: Compare dates within same table to set flag

quilldew — Thu, 25 May 2017 11:36:27 GMT

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.

Re: Compare dates within same table to set flag

quilldew — Fri, 26 May 2017 07:25:32 GMT

Can we bump our own threads?

Re: Compare dates within same table to set flag

jonathandienst — Fri, 26 May 2017 07:39:23 GMT

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

Re: Compare dates within same table to set flag

quilldew — Fri, 26 May 2017 07:57:20 GMT

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.

Re: Compare dates within same table to set flag

jonathandienst — Fri, 26 May 2017 09:14:10 GMT

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.

Re: Compare dates within same table to set flag

jonathandienst — Fri, 26 May 2017 09:41:17 GMT

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

;

Re: Compare dates within same table to set flag

quilldew — Fri, 26 May 2017 11:26:56 GMT

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