Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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***
Would it be possible for you to share a sample of your application to look at this?
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.