topic Re: Problem with selecting periods... in QlikView

Problem with selecting periods...

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

Hello,

I've got this source table:

EMPL_NO	DATE_FROM	DATE_TO	EMPL_DATE	CHANGE_DATE	PERIOD
4489	2006-10-11	2006-11-10	2006-10-11	2006-10-11	177758
4489	2006-10-11	2006-12-31	2006-10-11	2006-11-11	177758
4489	2007-01-01	2007-03-31	2007-01-01	2007-01-01	178319
4489	2007-04-01	2007-09-30	2007-04-01	2007-04-01	179654
4489	2007-10-01	2008-04-30	2007-10-01	2007-10-01	204028
4489	2007-10-01	2009-12-31	2007-10-01	2008-05-01	204028
4489	2007-10-01	2010-09-30	2007-10-01	2010-01-01	204028
4489	2007-10-01	2015-03-31	2007-10-01	2010-10-01	204028
4489	2007-10-01	2016-04-30	2007-10-01	2015-04-01	204028
4489	2007-10-01	_________	2007-10-01	2016-05-01	204028

Need to select first date_from in each period and last date_to in each period

Result table should be like that:

EMPL_NO	DATE_FROM	DATE_TO	PERIOD
4489	2006-10-11	2006-12-31	177758
4489	2007-01-01	2007-03-31	178319
4489	2007-04-01	2007-09-30	179654
4489	2007-10-01		204028

I'm new to QV so I ask for your understanding

Regards

Re: Problem with selecting periods...

MindaugasBacius — Fri, 22 Feb 2019 12:41:40 GMT

tmp:
LOAD EMPL_NO, 
     DATE_FROM, 
     If(DATE_TO = '_________', Today(), DATE_TO) as DATE_TO, 
     EMPL_DATE, 
     CHANGE_DATE, 
     PERIOD
FROM
[https://community.qlik.com/t5/New-to-QlikView/Problem-with-selecting-periods/m-p/1547936#M370501]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);


NoConcatenate
tmp2:
LOAD EMPL_NO
	,PERIOD
	,Min(DATE_FROM) as min_DATE_FROM
	,Max(DATE_TO) as max_DATE_TO
Resident tmp
Group By EMPL_NO
	,PERIOD
;

The script is above.

I have only changed value If(DATE_TO = '_________', Today(), DATE_TO) as DATE_TO. You can easily convert it back to '_________' instead of Today().

The result:

Re: Problem with selecting periods...

MindaugasBacius — Fri, 22 Feb 2019 12:47:35 GMT

tmp:
LOAD EMPL_NO, 
     DATE_FROM, 
     If(DATE_TO = '_________', Today(), DATE_TO) as DATE_TO, 
     EMPL_DATE, 
     CHANGE_DATE, 
     PERIOD
FROM
[https://community.qlik.com/t5/New-to-QlikView/Problem-with-selecting-periods/m-p/1547936#M370501]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);


NoConcatenate
tmp2:
LOAD EMPL_NO
	,PERIOD
	,Min(DATE_FROM) as min_DATE_FROM
	,If(Max(DATE_TO) = Today(), Null(), Max(DATE_TO))as max_DATE_TO
Resident tmp
Group By EMPL_NO
	,PERIOD
;

Re: Problem with selecting periods...

chrisk44 — Fri, 22 Feb 2019 13:22:06 GMT

seems that it is works.

with small correction in if clause:

if (isnull(DATE_TO),Today(),DATE_TO) as DATE_TO

Thanks a LOT

Regards