topic Re: set analysis include null dates in QlikView

set analysis include null dates

YoussefBelloum — Tue, 30 Mar 2021 11:33:45 GMT

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

Re: set analysis include null dates

Frank_Hartmann — Tue, 30 Mar 2021 13:15:19 GMT

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;

Re: set analysis include null dates

YoussefBelloum — Tue, 30 Mar 2021 13:46:00 GMT

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

Re: set analysis include null dates

Frank_Hartmann — Tue, 30 Mar 2021 14:20:04 GMT

Maybe like this:

Dim:

ID, TYPE

Exp1:

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

Exp2:

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

Re: set analysis include null dates

YoussefBelloum — Tue, 30 Mar 2021 14:43:27 GMT

this one is working fine:

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

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