Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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())
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;
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
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())
this one is working fine:
only({<DATE1={"=DATE1 <= '31/12/2020' "}>}DATE1)
what is the trick here ? how does it include the NULL lines ?