topic Set analysis or calculated field consisting STRING field with respect to DATE in QlikView

Set analysis or calculated field consisting STRING field with respect to DATE

wiz_nerd — Sat, 16 Nov 2024 04:41:38 GMT

I am Trying to create a dimension using these fields. But i just want data for latest "n" dates.

I want to used the Table field name on x axis with respect to last three dates per field.

Dates in RFC3339 format.

Table1_Field_NAME	Table2_Field_DATE

FIELD_A_01.1904_DB01	2019-01-11T03:08:07+01:00
FIELD_A_01.1904_DB03	2019-01-13T03:06:03+01:00
FIELD_A_01.1904_DB04	2019-01-14T03:09:21+01:00
FIELD_A_01.1904_DB06	2019-01-16T03:08:57+01:00
FIELD_A_01.1904_DB07	2019-01-17T03:12:40+01:00
FIELD_A_01.1905_DB01	2019-01-18T03:12:56+01:00

FIELD_B_01.1904_DB01	2019-01-11T03:08:07+01:00
FIELD_B_01.1904_DB03	2019-01-13T03:06:03+01:00
FIELD_B_01.1904_DB04	2019-01-14T03:09:21+01:00
FIELD_B_01.1904_DB06	2019-01-16T03:08:57+01:00
FIELD_B_01.1904_DB07	2019-01-17T03:12:40+01:00
FIELD_B_01.1905_DB01	2019-01-18T03:12:56+01:00

Re: Set analysis or calculated field consisting STRING field with respect to DATE

uacg0009 — Wed, 23 Jan 2019 08:00:24 GMT

Hi,

I think below is what you want:

I think firstly you need to create a new column for group by, for example,

left(Table1_Field_NAME,10) as new_column

and then in your chart, use below expression to be dimension:

=aggr(if(rank(left(Table2_Field_DATE,10)) < 4,Table1_Field_NAME),new_column,Table2_Field_DATE)

And suppress the null value, then you can get the top 3 NAME value based on DATE like below screenshot

Aiolos Zhao

Re: Set analysis or calculated field consisting STRING field with respect to DATE

wiz_nerd — Wed, 23 Jan 2019 12:35:07 GMT

Thanks Aiolos Zhao for your valuable response.

I have these doubts.

Well why did you use group_by here well and also i didn't understand the reason of using 10 ??

The issue is i cant modify the load script I do not have write access and these values are already dependent on each other so can we modify it somehow to get the desired result without changing the load script.

Re: Set analysis or calculated field consisting STRING field with respect to DATE

wiz_nerd — Wed, 23 Jan 2019 21:17:37 GMT

Hi Aiolos,
I have edited the question with more information and explanation since you have tried to help me and spend your valuable time I want you to look at the edited question if you want to look. This is the link to it.
https://community.qlik.com/t5/QlikView-App-Development/Creating-Dimension-with-respect-to-date-values-using-set/td-p/1534123