Access flags from canonical date table in set analysis
I I feel like I am taking advantage of the knowledge of people here. Sorry, if I abuse it. With me positing here, it doesn't mean that I am not trying myself but I figured that when I stuck absolutely, I should ask someone who has more expertise. It hurts my ego, but helps to get results faster.
Let's say that I need to calculate the sum of the berthing time for all vessel visits. I added the vessel visit (from both, the move and vessel tables) to the Bridge table (see the code below). So, how can I access the vessel visits ONLY those that have the tag 'vessel'? In the LinkTable, one row represents, roughly speaking, one container. Which means that one vessel visit could have several rows in the LinkTable.
but of course it doesn't work. I think it's because I am accessing the rows that I am interested in incorrectly. I have googled but have not found how to access the flags from the canonical date table or there is no difference from accessing flags in the ordinary set analysis?
LinkTable:
LOAD
m_row_id,
visit,
container,
Timestamp([TET],'MM/DD/YYYY hh:mm:ss tt') as TimeStamp,
'move' as type,
If(Time(Frac([TET])) >= ('06:00:00 AM') and Time(Frac([TET]))< ('02:00:00 PM'), 'Morning',
If(Time(Frac([TET])) >= ('02:00:00 PM') and Time(Frac([TET])) < ('10:00:00 PM'), 'Afternoon',
If(Time(Frac([TET])) >= ('10:00:00 PM') and Time(Frac([TET])) < ('11:59:59 PM'), 'Night',
If(Time(Frac([TET])) >= ('12:00:00 AM') and Time(Frac([TET])) < ('06:00:00 AM'), 'Night' ,0 )))) as shift
Resident Moves
;
Concatenate(LinkTable)
LOAD
v_row_id,
visit,
container,
Timestamp(`EST_DPTR_D`,'MM/DD/YYYY hh:mm:ss tt') as TimeStamp,
'vessel' as type,
If(Time(Frac(`EST_DPTR_D`)) >= ('06:00:00 AM') and Time(Frac(`EST_DPTR_D`))< ('02:00:00 PM'), 'Morning',
If(Time(Frac(`EST_DPTR_D`)) >= ('02:00:00 PM') and Time(Frac(`EST_DPTR_D`)) < ('10:00:00 PM'), 'Afternoon',
If(Time(Frac(`EST_DPTR_D`)) >= ('10:00:00 PM') and Time(Frac(`EST_DPTR_D`)) < ('11:59:59 PM'), 'Night',
If(Time(Frac(`EST_DPTR_D`)) >= ('12:00:00 AM') and Time(Frac(`EST_DPTR_D`)) < ('06:00:00 AM'), 'Night',0)))) as shift
Resident Vessel
;
Concatenate(LinkTable)
LOAD
g_row_id,
container,
Timestamp([TRACTOR_DEPART_DATE],'MM/DD/YYYY hh:mm:ss tt') as TimeStamp,
'gate' as type,
If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('06:00:00 AM') and Time(Frac([TRACTOR_DEPART_DATE]))< ('02:00:00 PM'), 'Morning',
If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('02:00:00 PM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('10:00:00 PM'), 'Afternoon',
If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('10:00:00 PM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('11:59:59 PM'), 'Night',
If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('12:00:00 AM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('06:00:00 AM'), 'Night',0)))) as shift
Resident Gate
;
DROP FIELD container, visit FROM Vessel, Moves, Gate;
I don't know if its a mistake or not as I don't know enough about your data. But if it work what is the issue. But if not maybe try changing things as I have noted below
Join data tables or Concatenate data tables
I don't do a bit of both (only part concatenate and join the linktables to the various data tables) as you have done. I know some do but to me it just complicates the model. But I don't see why it shouldn't work. (Maybe I will try this approach in the future. At least it shows more detail in the data model)
Derived Dates
Try taking this out and adding a Date to the link table (Date(floor( ----- )) as etc and joining a calendar to the link table. Not to the other fields as well.
Or adding a date field to the linktables >> (Date(floor( ----- )) . Although [TimeStamp] may work. It creates issues with a non derived date calendar. I'm unsure if likewise with the derived date calendars or not
but I don't like lots of derived calendars (as users can use the wrong calendar option). Do you need so many? And in general I don't like derived dates.
DERIVE FIELDS FROM FIELDS [TimeStamp],[TRACTOR_DEPART_DATE],[TET],[TRACTOR_ARRIVE_DATE],[WSTH], [COMP_MOVE],[LASTUPDATE],[DEPARTURE_LAST_LINE_TM], [EST_ARV_D], [EST_DPTR_D], [VSL_BERTH_D], [ACTL_ARV_D], [WORK_START_TM], [WORK_END_TM], [CNTR_ARRIVE_TM], [CNTR_DEPART_TM] USING [Calendar]
Aggr
Don't use if avoidable
Did my adjusted expression work
sum({$<type={'vessel'}>}
(TimeStamp-VSL_BERTH_D) *24)
RowNo()
RowNo() as g_row_id, etc
I always use say
'Gate' & RowNo() as g_row_id etc
as for some reason in the past I ended up with the same rowno() in two tables. So by putting a 'Gate' or 'g' at the beginning it prevents this (but as they model is used now it makes no difference)
