Solved: Dimenssion value based on MAX(DATE) - Qlik Community

Uri · ‎2022-12-28

Hi,

would appreciate if someone could help me with the below.

In the attached file, I want only 100A value to be presented for month 11-2022 and 5A and 10C to be hidden/removed.

BR,

Uri

Taoufiq_Zarra · ‎2022-12-29

@Uri if you are looking for the last one

you can use interface directly

dimension: A,B,C,E

measure :

F: if(isnull(Only({<c= {"$(=Date(Max(c), 'YYYY-MM-DD'))"}>} F)),F,Only({<c= {"$(=Date(Max(c), 'YYYY-MM-DD'))"}>} F))

c: =Date(Max(c), 'YYYY-MM-DD')

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Taoufiq_Zarra · ‎2022-12-28

@Uri if I understood correctly

you want just a if... else in the dimension F

you can share a sample data in Excel format and the output if you want

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Uri · ‎2022-12-28

Hi @Taoufiq_Zarra,

thanks for you reply.

This is the data sample:

A	B	C	E	F	F
2022	Q4-2022	11-2022	Company	5A	2022-11-28
2022	Q4-2022	11-2022	Company	10C	2022-11-29
2022	Q4-2022	11-2022	Company	100A	9999-12-31
2022	Q4-2022	10-2022	Company	5A	2022-11-28
2022	Q3-2022	09-2022	Company	5A	2022-11-28
2022	Q3-2022	08-2022	Company	5A	2022-11-28
2022	Q3-2022	07-2022	Company	5A	2022-11-28
2022	Q2-2022	06-2022	Company	5A	2022-11-28
2022	Q2-2022	05-2022	Company	5A	2022-11-28
2022	Q2-2022	04-2022	Company	5A	2022-11-28
2022	Q1-2022	03-2022	Company	5A	2022-11-28
2022	Q1-2022	02-2022	Company	5A	2022-11-28
2022	Q1-2022	01-2022	Company	5A	2022-11-28
2021	Q4-2021	12-2021	Company	5A	2022-11-28
2021	Q4-2021	11-2021	Company	5A	2022-11-28
2021	Q4-2021	10-2021	Company	5A	2022-11-28
2021	Q3-2021	09-2021	Company	5A	2022-11-28
2021	Q3-2021	08-2021	Company	5A	2022-11-28
2021	Q3-2021	07-2021	Company	5A	2022-11-28
2021	Q2-2021	06-2021	Company	5A	2022-11-28

The output i want:

A	B	C	E	F	F
2022	Q4-2022	11-2022	Company	100A	9999-12-31
2022	Q4-2022	10-2022	Company	5A	2022-11-28
2022	Q3-2022	09-2022	Company	5A	2022-11-28
2022	Q3-2022	08-2022	Company	5A	2022-11-28
2022	Q3-2022	07-2022	Company	5A	2022-11-28
2022	Q2-2022	06-2022	Company	5A	2022-11-28
2022	Q2-2022	05-2022	Company	5A	2022-11-28
2022	Q2-2022	04-2022	Company	5A	2022-11-28
2022	Q1-2022	03-2022	Company	5A	2022-11-28
2022	Q1-2022	02-2022	Company	5A	2022-11-28
2022	Q1-2022	01-2022	Company	5A	2022-11-28
2021	Q4-2021	12-2021	Company	5A	2022-11-28
2021	Q4-2021	11-2021	Company	5A	2022-11-28
2021	Q4-2021	10-2021	Company	5A	2022-11-28
2021	Q3-2021	09-2021	Company	5A	2022-11-28
2021	Q3-2021	08-2021	Company	5A	2022-11-28
2021	Q3-2021	07-2021	Company	5A	2022-11-28
2021	Q2-2021	06-2021	Company	5A	2022-11-28

So yes, I want the values 5A and 10C to be hidden/removed from dimension F for the month 11-2022.

Uri · ‎2022-12-28

@Taoufiq_Zarra thanks for your reply.

I want to keep only the latest value (in the column F) where the date is 9999-12-31.

So, the values 5A and 10C in column F should not be presented for month 11-2022.

Please see attached excel file.

BR,

Uri

Taoufiq_Zarra · ‎2022-12-28

@Uri the last value only for 9999-12-31 ?

what is the commun between 5A, 10C and 100A to create one formula ?

2022	Q4-2022	11-2022	Company	5A	2022-11-28
2022	Q4-2022	11-2022	Company	10C	2022-11-29
2022	Q4-2022	11-2022	Company	100A	9999-12-31

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Uri · ‎2022-12-29

@Taoufiq_Zarra there is one more field not visible here - ID_NUMBER which is unique. So, ID_NUMBER 111111 has changed subscription form (field F) during November 3 times. First 5A then 10C and finally 100A but Im only interested in the latest one.

BR,

Uri

Taoufiq_Zarra · ‎2022-12-29

@Uri if I understood correctly

you can for example in load script use this script :

DATA:


LOAD *,num(keepchar(F,0123456789)) as Fbis INLINE [
    A, B, C, E, F, c
    2022, Q4-2022, 11-2022, Company, 5A, 2022-11-28
    2022, Q4-2022, 11-2022, Company, 10C, 2022-11-29
    2022, Q4-2022, 11-2022, Company, 100A, 9999-12-31
    2022, Q4-2022, 10-2022, Company, 5A, 2022-11-28
    2022, Q3-2022, 09-2022, Company, 5A, 2022-11-28
    2022, Q3-2022, 08-2022, Company, 5A, 2022-11-28
    2022, Q3-2022, 07-2022, Company, 5A, 2022-11-28
    2022, Q2-2022, 06-2022, Company, 5A, 2022-11-28
    2022, Q2-2022, 05-2022, Company, 5A, 2022-11-28
    2022, Q2-2022, 04-2022, Company, 5A, 2022-11-28
    2022, Q1-2022, 03-2022, Company, 5A, 2022-11-28
    2022, Q1-2022, 02-2022, Company, 5A, 2022-11-28
    2022, Q1-2022, 01-2022, Company, 5A, 2022-11-28
    2021, Q4-2021, 12-2021, Company, 5A, 2022-11-28
    2021, Q4-2021, 11-2021, Company, 5A, 2022-11-28
    2021, Q4-2021, 10-2021, Company, 5A, 2022-11-28
    2021, Q3-2021, 09-2021, Company, 5A, 2022-11-28
    2021, Q3-2021, 08-2021, Company, 5A, 2022-11-28
    2021, Q3-2021, 07-2021, Company, 5A, 2022-11-28
    2021, Q2-2021, 06-2021, Company, 5A, 2022-11-28
];


output:
load A,B,C,E,max(Fbis) as Fbis resident DATA group by A,B,C,E;


left join load A,B,C,E,Fbis,F,c resident DATA; 

drop table DATA;
drop fields Fbis;

result from this input :

2022	Q4-2022	11-2022	Company	100A	9999-12-31
2022	Q4-2022	10-2022	Company	5A	2022-11-28
2022	Q3-2022	09-2022	Company	5A	2022-11-28
2022	Q3-2022	08-2022	Company	5A	2022-11-28
2022	Q3-2022	07-2022	Company	5A	2022-11-28
2022	Q2-2022	06-2022	Company	5A	2022-11-28
2022	Q2-2022	05-2022	Company	5A	2022-11-28
2022	Q2-2022	04-2022	Company	5A	2022-11-28
2022	Q1-2022	03-2022	Company	5A	2022-11-28
2022	Q1-2022	02-2022	Company	5A	2022-11-28
2022	Q1-2022	01-2022	Company	5A	2022-11-28
2021	Q4-2021	12-2021	Company	5A	2022-11-28
2021	Q4-2021	11-2021	Company	5A	2022-11-28
2021	Q4-2021	10-2021	Company	5A	2022-11-28
2021	Q3-2021	09-2021	Company	5A	2022-11-28
2021	Q3-2021	08-2021	Company	5A	2022-11-28
2021	Q3-2021	07-2021	Company	5A	2022-11-28
2021	Q2-2021	06-2021	Company	5A	2022-11-28

the output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Uri · ‎2022-12-29

Thanks! @Taoufiq_Zarra is it possible to solve this in the table directly?

Taoufiq_Zarra · ‎2022-12-29

@Uri if you are looking for the last one

you can use interface directly

dimension: A,B,C,E

measure :

F: if(isnull(Only({<c= {"$(=Date(Max(c), 'YYYY-MM-DD'))"}>} F)),F,Only({<c= {"$(=Date(Max(c), 'YYYY-MM-DD'))"}>} F))

c: =Date(Max(c), 'YYYY-MM-DD')

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Uri · ‎2022-12-29

@Taoufiq_Zarra thank you! It works perfectly! 🙂

Dimenssion value based on MAX(DATE)

Chart

dimension