How to create a flag for null Date values

DaithiOK — Sat, 16 Nov 2024 00:29:41 GMT

I want to create a flag to denote a project that has never had an update. In my dataset one project can appear multiple times if it's had multiple updates and [Last Update Date] will give the date of that update. I am using my [Last Update Date] in a different flag but realize i am missing out on all those projects that don't get counted because they don't have a [Last Update Date].

How can I flag the projects where the [Last Update Date] is null because it has never been updated.

In the load script right after [Last Update Date] I tried the line

if(isnull([Last Update Date]),1,0)

but that only flags when the column [Last Update Date] is null rather than when that column is null for a particular project.

Right now I'm trying

Null_Flag_Test: Load [Project Code], date(MONTHSTART(max([Last Update Date])),'DD/MM/YYYY') as MaxUpdateDate, if(len(date(MONTHSTART(max([Last Update Date])),'YYYY-MMM')),0,1) From [..DataSource.qvd] (qvd) group by [Project Code];

but it doesnt seem to work either. I figure that given there are multiple update dates that this table would give me the project code and its most recent [Last Update Date] and if the column was null then there would be no Max so it would get flagged in the third column. It doesnt seem to work though. Can anyone offer suggestions?

Re: How to create a flag for null Date values

vamsee — Thu, 11 Jun 2020 19:59:18 GMT

Hello,

Firstly, in both the if statements you are missing the condition.

Try

if(isnull([Last Update Date])=-1,1,0)

Or I would suggest

if(len(Max([Last Update Date]))<>0,0,1) as Missing_Update

All 1's would give you projects with no update date.

Re: How to create a flag for null Date values

Saravanan_Desingh — Thu, 11 Jun 2020 21:25:35 GMT

To capture the NULL, you can try all these options.

If(isNull([Last Update Date]) Or [Last Update Date]='' Or Len(Trim([Last Update Date]))=0 ,1,0)

topic Re: How to create a flag for null Date values in QlikView

How to create a flag for null Date values

Re: How to create a flag for null Date values

Re: How to create a flag for null Date values