set analysis include null dates

[YoussefBelloum]

Hi guys,

I'm struggling on counting lines with dates = null

here is a sample below:

ID, TYPE, DATE1, DATE2
1, A, 31/12/2021, 31/12/2025
2, A, 31/12/2022, 31/12/2024
3, A, 31/12/2023, 31/12/2024
4, A, 31/12,2020, 31/12/2030
5, A, 31/12,2020, 31/12/2040
6, A, 31/12,2020, 31/12/2050
7, A, 31/12,2020,
8, A, 31/12,2020,
9, A, 31/12,2020,
10, A, ,
11, A, ,
12, A, ,

I need to count the ID's where DATE1 <= 31/12/2020 including lines where DATE1 and DATE2 are null also

The result should be this:

4, A, 31/12,2020, 31/12/2030
5, A, 31/12,2020, 31/12/2040
6, A, 31/12,2020, 31/12/2050
7, A, 31/12,2020,
8, A, 31/12,2020,
9, A, 31/12,2020,
10, A, ,
11, A, ,
12, A, ,

count(ID)= 9 

I tried with some set analysis using  NULL FLAG columns creating on the script ( using len trim) ) but it is not working..

Thank you for your help

Regards,

Youssef B

[Frank_Hartmann]

Maybe like this?

tmp:
Load * Inline [
ID, TYPE, DATE1, DATE2
1, A, 31/12/2021, 31/12/2025
2, A, 31/12/2022, 31/12/2024
3, A, 31/12/2023, 31/12/2024
4, A, 31/12/2020, 31/12/2030
5, A, 31/12/2020, 31/12/2040
6, A, 31/12/2020, 31/12/2050
7, A, 31/12/2020,
8, A, 31/12/2020,
9, A, 31/12/2020,
10, A, ,
11, A, ,
12, A, ,];

NoConcatenate

Load * Resident tmp Where DATE1<= '31/12/2020' ;DROP Table tmp;

[YoussefBelloum]

Hi @Frank_Hartmann 

Thank you for your reply,

Unfortunately I can't add a where() on the script because I need all the lines for other measures.

I can only create calculated fields and use a set analysis to make it work

[Frank_Hartmann · Accepted solution]

Maybe like this:

tmp:
Load * Inline [
ID, TYPE, DATE1, DATE2
1, A, 31/12/2021, 31/12/2025
2, A, 31/12/2022, 31/12/2024
3, A, 31/12/2023, 31/12/2024
4, A, 31/12/2020, 31/12/2030
5, A, 31/12/2020, 31/12/2040
6, A, 31/12/2020, 31/12/2050
7, A, 31/12/2020,
8, A, 31/12/2020,
9, A, 31/12/2020,
10, A, ,
11, A, ,
12, A, ,];

Dim:

ID, TYPE

Exp1:

only({<DATE1={"=DATE1 <= '31/12/2020' "}>}DATE1)

Exp2:

if(only({<DATE1={"=DATE1 <= '31/12/2020' "}>}DATE1)<>null(),DATE2,null())

[YoussefBelloum]

this one is working fine: 

only({<DATE1={"=DATE1 <= '31/12/2020' "}>}DATE1)

what is the trick here ?  how does it include the NULL lines ?