How to identify and create period missing ?

[suzel404]

Hi all,

How can I identify and create missing period?

ID	Start	End
1	17/08/2016	18/06/2016
1	18/06/2016	20/10/2016
-99	20/01/2016	01/11/2016
1	01/11/2016	31/12/2016
-99	31/12/2016	02/03/2017
1	02/01/2017	03/03/2017

Thanks for your help.

[Chanty4u]

chk dis

How to find a missing Row while loading data from Excel

[sunny_talwar]

May be something like this:

Table:

LOAD * Inline [

ID, Start, End

1, 17/04/2016, 18/06/2016

1, 18/06/2016, 20/10/2016

1, 01/11/2016, 31/12/2016

1, 02/01/2017, 03/03/2017

];

Temp:

LOAD *,

  If(RowNo() > 1, If(Peek('End') = Start, 1, 0)) as Flag,

  If(RowNo() > 1, If(Peek('End') = Start, Null(), Peek('End'))) as NewStart,

  Start as NewEnd

Resident Table

Order By ID, Start;

Concatenate(Table)

LOAD -99 as ID,

  NewStart as Start,

  NewEnd as End

Resident Temp

Where Flag = 0;

DROP Table Temp;

[suzel404]

Hi Sunny,

I tried with my data but I have null Mark_ID.

Can you help me ?

Thanks

[sunny_talwar]

I will check this at a later point today.

Best,

Sunny