How to Create Start & End Date using Date Field

[L_Hop]

Dear Qlikies,

Trying to create 'Start Date' & 'End Date' using 'ODDMTJ' field for each row.

I am having trouble to gettting 'End Date'. End date should be next ODDMTJ value for ODURAB&ODDMTJ group.

I have tried load the original table than left join table with using key 'ODURAB&ODDMTJ' , previous(ODDMTJ) as  'End date' with sort order desc. Last value of the grouped date mostly not expected.

How can I accomplish this task on script side. 

ODURAB	ODDMTJ	ODPAAP
2361	19/01/2017	-393151.2
2361	19/01/2017	15726046.8
2361	06/02/2017	-70981824
2361	06/02/2017	-514471.2
2361	06/02/2017	20553.6
2361	06/02/2017	493917.6
2361	11/05/2017	1785331.2
2361	11/05/2017	49537047.6

Any help would be greatly appreciated.

Thanks in advanced.

[dplr-rn · Accepted solution]

try soemthing like below script

MyTable:
load * inline [
ODURAB,	ODDMTJ,	ODPAAP
2361,	19/01/2017,	-393151.2
2361,	19/01/2017,	15726046.8
2361,	06/02/2017,	-70981824
2361,	06/02/2017,	-514471.2
2361,	06/02/2017,	20553.6
2361,	06/02/2017,	493917.6
2361,	11/05/2017,	1785331.2
2361,	11/05/2017,	49537047.6
2362,	07/02/2017,	12324.6
2362,	13/05/2017,	6744.2
];

temp:
load Distinct ODURAB,	ODDMTJ
resident MyTable
order by ODURAB desc,	ODDMTJ desc;
timeframes:
load *
	,ODDMTJ as StrtDate
    , if(Previous(ODURAB)=ODURAB,previous(ODDMTJ)) as EndDate
resident temp    ;
drop table temp;

Output

ODURAB	ODDMTJ	ODPAAP	StrtDate	EndDate
2361	11/05/2017	1785331.2	11/05/2017	06/02/2017
2361	11/05/2017	49537047.6	11/05/2017	06/02/2017
2361	06/02/2017	-70981824	06/02/2017	19/01/2017
2361	06/02/2017	-514471.2	06/02/2017	19/01/2017
2361	06/02/2017	20553.6	06/02/2017	19/01/2017
2361	06/02/2017	493917.6	06/02/2017	19/01/2017
2361	19/01/2017	-393151.2	19/01/2017	-
2361	19/01/2017	15726046.8	19/01/2017	-
2362	07/02/2017	12324.6	07/02/2017	13/05/2017
2362	13/05/2017	6744.2	13/05/2017	-

[Vegar]

Try something  like this. 

Intervals:

Load  distinct

ODURAB, ODDMTJ,

ODDMTJ as validTo, 

If(peek('ODDRAB')=ODDRAB, PEEK('ODDMTJ') AS validTo

Resident  Data

Order by 

ODURAB, ODDMTJ desc;

Left join Data load * from Intervals;

Drop table Intervals

[L_Hop]

Dear Dilipranjith,

Thank you for your kind help.

Script worked as expected.

best regards;