Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chrisk44
Contributor III
Contributor III

Problem with selecting periods...

Hello,

I've got this source table:

 

EMPL_NO DATE_FROM DATE_TO  EMPL_DATE CHANGE_DATE PERIOD 
44892006-10-112006-11-102006-10-112006-10-11177758
44892006-10-112006-12-312006-10-112006-11-11177758
44892007-01-012007-03-312007-01-012007-01-01178319
44892007-04-012007-09-302007-04-012007-04-01179654
44892007-10-012008-04-302007-10-012007-10-01204028
44892007-10-012009-12-312007-10-012008-05-01204028
44892007-10-012010-09-302007-10-012010-01-01204028
44892007-10-012015-03-312007-10-012010-10-01204028
44892007-10-012016-04-302007-10-012015-04-01204028
44892007-10-01 _________                  2007-10-012016-05-01204028

 

 

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 
44892006-10-112006-12-31177758
44892007-01-012007-03-31178319
44892007-04-012007-09-30179654
44892007-10-01 204028

 

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

 

Regards

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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
;

Screenshot_1.jpg

View solution in original post

3 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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:

Screenshot_1.jpg

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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
;

Screenshot_1.jpg

chrisk44
Contributor III
Contributor III
Author

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