Removing Unique ID's

[Mahamed_Qlik]

Hi

I have data as below, I want to consider only those ID which do not have NULL date 

ID	DATE	STATUS
101	1/7/2023	NEW
101	2/7/2023	RE-JOIN
101		RE-JOIN
102	3/7/2023	NEW
103	4/7/2023	RE-JOIN

In above table, 101 ID has one NULL so I want to exclude all the rows for this ID.

I want result as below :

102	3/7/2023	NEW
103	4/7/2023	RE-JOIN

[Or]

You'd probably want to use something like

if(NullCount(Total <ID> DATE)=0,DATE) as your measure and a similar one for status, and make sure that you hide zero/null.

[BrunPierre · Accepted solution]

Hi, a script solution as below.

Left Join
load ID,
Only(If(Len(Trim(DATE)) = 0, 1)) as NullFlag
Resident SourceTable
Group By ID;

NoConcatenate
Data:
LOAD * Resident SourceTable
Where NullFlag <> 1;

DROP Table SourceTable;
DROP Field NullFlag;

[Mahamed_Qlik]

Perfect !!!

In addition to this, what if I want to get max date for each ID including above condition?

[sergio0592]

Hi, 

You can add dimension in a chart with :

=if(aggr(sum(if(len(STATUS)=0,1,0)),ID)=0,'')