Date comparison does not work in LOAD!!!

[#1]

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;

[swuehl]

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

[#3]

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.

[Peter_Cammaert · Accepted solution]

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

[#5]

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.

[Gysbert_Wassenaar]

See this blog post: Preceding Load

And this document: Joins and Lookups