Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
YoussefBelloum
Champion
Champion

set analysis include null dates

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

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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())

Frank_Hartmann_0-1617113986732.png

 

 

View solution in original post

4 Replies
Frank_Hartmann
Master II
Master II

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;

Frank_Hartmann_0-1617110104996.png

 

YoussefBelloum
Champion
Champion
Author

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
Master II
Master II

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())

Frank_Hartmann_0-1617113986732.png

 

 

View solution in original post

YoussefBelloum
Champion
Champion
Author

this one is working fine: 

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

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