topic Re: Date comparison does not work in LOAD!!! in QlikView

Date comparison does not work in LOAD!!!

Fri, 27 Dec 2013 22:56:25 GMT

Hi,

Happy holidays!!!

I am trying to create a new data field based upon result of date comparison. It does not work the way I expected. The LEFT JOIN LOAD gave me the duplicate records. The code and the screenshot are as below. I would like to know what I did wrong. Thanks

left join (Asthmap_patients)
load HOSPITALACCOUNTID,
if(isnull(IMMUNE_DATE),0,1) as FlushotFlag
,if (
isnull(IMMUNE_DATE),'No',
if(IMMUNE_DATE <admitdts, 'Before', if(IMMUNE_DATE<=DischargeDT and IMMUNE_DATE >=admitdts, 'InHospital','After'))
) as FlushotTime
Resident Asthmap_patients;

Re: Date comparison does not work in LOAD!!!

swuehl — Sat, 28 Dec 2013 00:53:59 GMT

There is probably a Join key missing, what about IMMUNE_DATE? I would also consider a preceding load instead of the join.

Re: Date comparison does not work in LOAD!!!

Sat, 28 Dec 2013 03:51:12 GMT

If I removed the new data field statement (see below), there are only two records instead of four.

left join (Asthmap_patients)
load HOSPITALACCOUNTID,
if(isnull(IMMUNE_DATE),0,1) as FlushotFlag
Resident Asthmap_patients;

It looks like the statement below cuased this problem.

,if (
isnull(IMMUNE_DATE),'No',
if(IMMUNE_DATE <admitdts, 'Before', if(IMMUNE_DATE<=DischargeDT and IMMUNE_DATE >=admitdts, 'InHospital','After'))
) as FlushotTime

The IMMUNE_DATE is used to determine when flu shot is administered by comparing with admission date and discharge date. I have to use join since the data came from two different databases - Oracle and SQL server.

Re: Re: Date comparison does not work in LOAD!!!

Peter_Cammaert — Sat, 28 Dec 2013 11:35:39 GMT

No, you don't need a JOIN because you try to join Asthmap_Patients to itself. Avoid JOINs if you can, they take too much time and eat memory.

Stefan is right, use a Preceding Load or add the new field while copying asthmap_patients to a temporary copy, then drop the original and rename the temporary copy (which in effect is the same as a Preceding Load)

For example:

TempAsthmap_patients:

noconcatenate load *,
if(isnull(IMMUNE_DATE),0,1) as FlushotFlag
,if (
isnull(IMMUNE_DATE),'No',
if(IMMUNE_DATE <admitdts, 'Before', if(IMMUNE_DATE<=DischargeDT and IMMUNE_DATE >=admitdts, 'InHospital','After'))
) as FlushotTime
Resident Asthmap_patients;

DROP Table Asthmap_patients;

RENAME Table TempAsthmap_patients to Asthmap_patients;

Best,

Peter

Re: Re: Date comparison does not work in LOAD!!!

Sat, 28 Dec 2013 12:30:05 GMT

Peter and Swuehl ,

Your answer solved my problem. Thanks.

Could you please give me more insights about preceding loading and what is the difference between Join and Preceding loading? Thanks again.

Re: Date comparison does not work in LOAD!!!

Gysbert_Wassenaar — Sat, 28 Dec 2013 13:01:29 GMT

See this blog post: Preceding Load

And this document: Joins and Lookups