Load only rows where dates match

[Ciara]

I'm trying to join tables in my Data Load Editor but only where the Date in one table is equal to the Date in the other.

Here's a condensed version of my script

EDVisits:

LOAD
VisitID,
Date(Floor(ServiceDateTime), 'DD/MM/YYYY')      as EDVisitDate,
Time(ServiceDateTime) as ServiceTime,
AccountNumber,
Status,
ApplyMap('Map_LocationID_Name', LocationID, 'N/A') as Location,
Name,
ReasonForVisit,
Sex,
Age(Today(), [BirthDateTime]) as Age,
UnitNumber
FROM [lib://EQvdFilesLive/Incremental/ADM/E_AdmVisits.qvd] (qvd)
WHERE ServiceDateTime > '2019-08-31 23:59:00' and (WildMatch(Status, '*ER') or WildMatch(PriorOutpatientStatus, '*ER'));

NoConcatenate
Radiology:
LOAD
VisitID,
OrderID,
CategoryID,
Status                                                                                                    as RadiologyOrderStatus,
Date(Floor(ServiceDateTime), 'DD/MM/YYYY')                    as RadiologyExamServiceDate
FROM [lib://EQvdFilesLive/Incremental/ITS/E_ItsOrder.qvd](qvd)
Where ServiceDateTime > '2019-09-01 00:00:00' and Status = 'R' and Match(CategoryID, 'CT','MRI','RAD','US');

Left Join(AdmProviderED)
Load
VisitID,
OrderID,
CategoryID,
RadiologyOrderStatus,
RadiologyExamServiceDate
Resident Radiology
Where RadiologyExamServiceDate = Date(Floor(ServiceDateTime), 'DD/MM/YYYY');
Drop Table Radiology;

When I'm trying to join the tables where RadiologyExamServiceDate equals the EDVisitDate it obviously doesn't recognise the ServiceDateTime from the original table.

Just wondering how I would write this?

Thanks

Ciara

[Ciara · Accepted solution]

Think I found a way.  Not sure its all that elegant but here's my script.  Validation suggests its working.

NoConcatenate
Radiology:
LOAD
VisitID,
OrderID,
CategoryID,
Status as RadiologyOrderStatus,
Date(Floor(ServiceDateTime), 'DD/MM/YYYY') as RadiologyExamServiceDate
FROM [lib://EQvdFilesLive/Incremental/ITS/E_ItsOrder.qvd](qvd)
Where ServiceDateTime > '2019-09-01 00:00:00' and Status = 'R' and Match(CategoryID, 'CT','MRI','RAD','US');

Right Join(Radiology)
Load
VisitID,
ServiceDate
Resident AdmProviderED;

Temp:
Load *,
If(RadiologyExamServiceDate = ServiceDate, 1,0) as RadSameDay
Resident Radiology;
Drop Table Radiology;

Left Join(AdmProviderED)
Load *
Resident Temp
Where RadSameDay = 1;
Drop Table Temp;

[AbhijitBansode]

Exists function can be used at such cases:

Where exists( EDVisitDate,RadiologyExamServiceDate)