If date_end and date_start are the same then...

[Qliksense_77]

Hi,

I am looking for a solution in the load script. 

If date_end are the same as date_start in the previous row,  and the ID are the same as in the previous row, then I only want 1 row with start_Date from the first row and end_date from the last row.

An example below. The first 3 rows should be seen as part of the same contact, because date_start is the same as date_end in the previous row - and also the ID are the same as in the previous row.

Is this possible ?

ID	RowNo	Date_start	Date_end	Number of contacts
1	1	11-02-2023	11-02-2023	1
1	2	11-02-2023	12-02-2023	1
1	3	12-02-2023	13-02-2023	1
1	4	22-02-2023	25-02-2023	1

Wanted result:

ID	RowNo	Date_start	Date_end	Number of contacts
1	1	11-02-2023	13-02-2023	1
1	2	22-02-2023	25-02-2023	1

[Gabbar · Accepted solution]

Try this:

A:
Load * From Source;

NoConcatenate
B:
Load *,If(ID=Previous(ID) and Date_start=Previous(Date_end),peek(S_Date),Date_start) as S_Date
Resident A order by ID,Date_start asc,Date_end asc;

Drop Table A;

NoConcatenate
C:
load *,If(ID=Previous(ID) and Date_end=Previous(Date_start),peek(E_Date),Date_end) as E_Date
Resident B order by ID asc, Date_end desc,Date_start desc;

Drop Table B;

NoConcatenate
Load distinct ID,S_Date as Date_start,E_Date as Date_end,"Number of contacts"
Resident C;

Drop Table C;

NoConcatenate
Main:
Load *,RowNo() as RowNo Resident D;

Drop Table D;

[Qliksense_77]

Thank you, perfect:)