topic Re: Min and max dates in QlikView

Min and max dates

annanirvin — Wed, 25 Nov 2020 16:16:04 GMT

Hi all.

I have a table like this

EID	FROM	TO
A	2017-12-21	2019-05-07
A	2018-05-08	2018-05-08
A	2018-05-09	2018-07-31
A	2018-08-01	2019-09-30
B	2018-04-03	2018-07-27
B	2018-08-27	2019-01-24
B	2018-08-27	2019-07-31

and I would like the result look like this

EID	FROM	TO
A	2017-12-21	2019-09-30
B	2018-08-27	2019-07-31

My coding skills are limited, is there anyone who can guide me here, please?

Re: Min and max dates

santhiqlik — Fri, 25 Jan 2019 04:57:04 GMT

Hi, Do you want to display Min or Max From date ? I see that you are displaying Min From date for EmpId A and Max From date for EmpID B. Could you please clarify.

Re: Min and max dates

jonathandienst — Fri, 25 Jan 2019 05:22:19 GMT

Create a table/pivot with EID as the dimension; Min(FROM) and Max(TO) as expressions/measures.

Re: Min and max dates

annanirvin — Fri, 25 Jan 2019 07:11:56 GMT

Sorry, I was unclear. I want Min FROM date for the last unbroken date range, which for B also happens to be Max FROM date.

Re: Min and max dates

annanirvin — Fri, 25 Jan 2019 07:15:52 GMT

Thanks, but wouldn't that give me 2018-04-03 for B which is not the date I want?

Re: Min and max dates

nilaksh92 — Mon, 28 Jan 2019 16:06:05 GMT

Please have a look over the attached qvw file.

This might solve your problem.

Thanks

Nilaksh Mahajan

Re: Min and max dates

annanirvin — Tue, 29 Jan 2019 05:32:17 GMT

Thanks, but that's not really what I'm looking for. Max for B should be 2018-08-27.

Any other suggestions?

Re: Min and max dates

nilaksh92 — Tue, 29 Jan 2019 13:50:37 GMT

Please find the attached file. (Final Table)

I am able to achieve what you need.

Re: Min and max dates

annanirvin — Tue, 29 Jan 2019 17:01:15 GMT

Thanks again for your efforts, but it's still not really the solution I'm looking for, I have hundreds of IDs and thousands of rows in my table.