Bridge table for date not working as expected, mapping tables

[divya_anand]

Hi all,

So I am trying to create a single field out of the 3 different timestamps that are in scope, which I can link to my calendar. When I execute the below script, and select "DateCompleted" or "DateScheduled" in BType field, it is filtering the data in EVT_COMPLETED/EVT_SCHEDEND correctly. However, when I choose "DateBooked" in Btype and when I check the field BOO_DATE, for some reason the data is not filtered properly (see the snapshot below). What could be the reason for such behavior?

Is it because BOO_DATE is from a different table ?

Map_Date_Completed:
Mapping Load [%EVT_CODE_BOO_EVENT], [EVT_COMPLETED] Resident R5EVENTS;

Map_Date_SCHEDULED:
Mapping Load [%EVT_CODE_BOO_EVENT], [EVT_SCHEDEND] Resident R5EVENTS;

Map_Date_Booked:
Mapping Load [%EVT_CODE_BOO_EVENT], timestamp([BOO_DATE]) Resident R5BOOKEDHOURS;

BridgeTable:
Load [%EVT_CODE_BOO_EVENT], ApplyMap('Map_Date_Completed', [%EVT_CODE_BOO_EVENT], Null()) as %Date,
'DateCompleted' as BType Resident R5EVENTS;

Load [%EVT_CODE_BOO_EVENT], ApplyMap('Map_Date_SCHEDULED', [%EVT_CODE_BOO_EVENT], Null()) as %Date,
'DateScheduled' as BType Resident R5EVENTS;

Load [%EVT_CODE_BOO_EVENT], ApplyMap('Map_Date_Booked', [%EVT_CODE_BOO_EVENT], Null()) as %Date,
'DateBooked' as BType Resident R5BOOKEDHOURS;


Left Join (BridgeTable)
Load Distinct %Date , day(%Date) as BDay,Month(%Date) as BMonth, Year(%Date) as BYear Resident BridgeTable;

***script ends here***

Btype=DateCompleted

BType=DateScheduled

BType=DateBooked - So here why is 2004 shown in BOO_DATE when BYear=2003 and BType=DateBooked?

FYI-

Thank you.

[vinieme12]

Applymap() will return the first date found  so i guess in this case your event_codes have been on since 2002 so it is returning that date.

Try below

BridgeTable:
Load [%EVT_CODE_BOO_EVENT], [EVT_COMPLETED]  as %Date,
'DateCompleted' as BType Resident R5EVENTS;

concatenate
Load [%EVT_CODE_BOO_EVENT], [EVT_SCHEDEND] as %Date,
'DateScheduled' as BType Resident R5EVENTS

concatenate
Load [%EVT_CODE_BOO_EVENT], timestamp([BOO_DATE]) as %Date,
'DateBooked' as BType Resident R5BOOKEDHOURS;

Left Join (BridgeTable)
Load Distinct %Date , day(%Date) as BDay,Month(%Date) as BMonth, Year(%Date) as BYear Resident BridgeTable;

***script ends here***

[sunny_talwar]

Would it be possible for you to share a sample of your application to look at this?

[divya_anand · Accepted solution]

Looks like the situation was that a few %EVT_CODE_BOO_EVENT have 2 values in BOO_DATE i.e, 1:2 relation. Hence the corresponding BOO_DATEs are displayed based on the association.

Thank you all for your help.