Start and end dates in selected periods

[chrisk44]

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

[Qlikas]

Hi,

I think there might be a better way to do that the line with empty date_to value would be empty, but it works:

LOAD Distinct
EMPL_NO,
PERIOD,
DATE(MIN(DATE_FROM),'YYYY-MM-DD') as min_date_from,
IF(LEN(TRIM(MaxString(DATE_TO)))= 0,' ', DATE(MAX(DATE_TO),'YYYY-MM-DD')) as max_date_to
RESIDENT table_name
GROUP BY
EMPL_NO,
PERIOD;

[chrisk44]

Thanks a lot - however result is not quite like it should be

After scripting result is:

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	2016-04-30	204028

And should be:

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

Last period is empty - that's the trick 😞

Regards

[Qlikas]

Is it realy empty or underscore symbol ____ ?
Well, I made inline table with empty value and it worked. Full code:

table_name:
load * inline
[
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
];

table_name2:
LOAD Distinct
EMPL_NO,
PERIOD,
DATE(MIN(DATE_FROM),'YYYY-MM-DD') as min_date_from,
IF(LEN(TRIM(MaxString(DATE_TO)))= 0,' ', DATE(MAX(DATE_TO),'YYYY-MM-DD')) as max_date_to
RESIDENT table_name
GROUP BY
EMPL_NO,
PERIOD;

DROP TABLE table_name;

[chrisk44]

Correct in inline load - it works - however in load from source table it doesn't...

These data fields are oracle table DATE fields and these empty fields are null i've still got date in last row 😞

[chrisk44]

In my database these if clause returns value 15.

for checking i've loaded source table with only null values in date_to column and it loaded correctly...

Regards

[Qlikas]

Well, you can change the null value to ' ' while loading data with formula

if(isnull(DATE_TO),' ',DATE_TO) as DATE_TO

and then just use my script.

[chrisk44]

now i've got error:

Invalid expression
table2:
LOAD Distinct
EMPL_NO,
PERIOD,
DATE(MIN(DATE_FROM),'YYYY-MM-DD') as min_date_from,
IF(isnull(DATE_TO),'',DATE_TO) as max_date_to
RESIDENT table GROUP BY
EMPL_NO,PERIOD

[Qlikas]

No, you should use if(isnull(DATE_TO),' ',DATE_TO) as DATE_TO in initial load sentence

Something like that
LOAD
EMPL_NO,
DATE_FROM,
IF(isnull(DATE_TO),'',DATE_TO) as max_date_to,
EMPL_DATE,
CHANGE_DATE,
PERIOD
...

[chrisk44]

Still - got no expected results...

My whole script:

[TABLE]:

load
IF(isnull(DATE_TO),null(),date(DATE_TO)) as max_date_to,
EMPL_NO,
PERIOD,
DATE_FROM,
DATE_TO,

SELECT *

FROM "TABLE";

LOAD Distinct
EMPL_NO,
PERIOD,
DATE(MIN(DATE_FROM),'YYYY-MM-DD') as min_date_from
RESIDENT TABLE GROUP BY
EMPL_NO,PERIOD;

and results are:

EMPL_NO	min_date_from	max_date_to	PERIOD
4489	2006-10-11	10.11.2006	177758
4489	2006-10-11	31.12.2006	177758
4489	2007-01-01	31.03.2007	178319
4489	2007-04-01	30.09.2007	179654
4489	2007-10-01	30.04.2008	204028
4489	2007-10-01	31.12.2009	204028
4489	2007-10-01	30.09.2010	204028
4489	2007-10-01	31.03.2015	204028
4489	2007-10-01	30.04.2016	204028
4489	2007-10-01		204028