Filtering with WHERE

[ClareChinonye]

Hi,

Please i'm trying to join the ServiceEvent event table to the asset history table where EventStartTime <= StartTime
And
(EventStartTime <= EventEndTime or IsNull(EventEndTime)). but it is throwing me an error

Left join (AssetStatusHistory)
LOAD EventID,
Username,
WorksOrder,
StartTime as EventStartTime,
EndTime as EventEndTime,
HasHandover

Resident ServiceEvent
Where
EventStartTime <= StartTime
And
(EventStartTime <= EventEndTime or IsNull(EventEndTime));

Can someone please help?

[Or]

This seems to have two aspects of the same issue:

1) You need to refer to fields using the name they're loaded with, not with the alias you've set in the Load section.  In this case, you would use StartTime and EndTime.

2) You seem to be referring to the same field on both ends of the condition - EventStartTime <= StartTime are both StartTime (one is the original name and the other is the alias), so this doesn't seem to make any sense?

[ClareChinonye]

I have columns named StartTime in both tables.

The ServiceEvent table has a StartTime column for event start time, and the AssetStatusHistory table has a seperate StartTime column for a particular column start time. how can i reference the table name to distinguish them

[Or]

The Where condition does not reference any tables other than the one being loaded.

It sounds like you're trying to do something like this:

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

https://community.qlik.com/t5/Design/IntervalMatch/ba-p/1464547

[ClareChinonye]

I am trying to write this SQL query  in Qlik Sense scripting language but i have been getting errors so far. I need help please

select assetstatushistory.WorksOrder, AssetStatusHistory.StartTime, ServiceEvent.StartTime as event_start, ServiceEvent.EndTime, SubStatus.SubStatusName
from AssetStatusHistory
left join
ServiceEvent on AssetStatusHistory.WorksOrder = ServiceEvent.WorksOrder
AND ServiceEvent.StartTime >= AssetStatusHistory.StartTime
AND ServiceEvent.StartTime <= COALESCE(AssetstatusHistory.EndTime, GETDATE())