Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
harleen_singh
Creator III
Creator III

Not able to handle null value in Interval match

Hello,

           I have a table kind of this

EmployeeID  TerritoryID  StartDate  EndDate

275                      2              7/1/2006    12/31/2007

275                      3              1/1/2008

Here in second row you can see Endate is blank

Other table i have is Order table having OrderDate,  its sales etc.

I am applying statement

IntervalMatch(OrderDate) LOAD StartDate as StartDate, EndDate from table


But, when i am caluclating sum or count of employee. It is calculating only for the interval where Enddate is given. But wat about where Enddate is not given?

Regards

Lavi

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Well you could check if EndDate is not given on load and set an EndDate far in the future, like

= if(isNull(EndDate)=-1,MakeDate(2199,12,31),EndDate) as EndDate

Regards,

Stefan

P.S: instead of the MakeDate, I think today() or yearend(today()) might also be sensibel and more flexibel.

View solution in original post

1 Reply
swuehl
MVP
MVP

Well you could check if EndDate is not given on load and set an EndDate far in the future, like

= if(isNull(EndDate)=-1,MakeDate(2199,12,31),EndDate) as EndDate

Regards,

Stefan

P.S: instead of the MakeDate, I think today() or yearend(today()) might also be sensibel and more flexibel.