Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
Thank you, perfect:)