Complex scenario generating records based on sequence

[rob_vander]

Hi All,

I have below source table

1	Alive	1	100
2	Sleep	10	25
3	Sleep	50	60
4	Sleep	70	81

Based on the state of sleep i need to fill in alive state matching with start seq & end seq so that it avoids any overlapping which should create below table

Row	state	start seq	end seq
1	Alive	1	9
2	Sleep	10	25
3	Alive	26	49
4	Sleep	50	60
5	Alive	61	69
6	Sleep	70	81
7	Alive	82	100

I don't know how do I start writing script for this scenario. If someone could point me to the right direction would be helpful

[ChannaK]

bro try this

Base:
load * inline [Sno,Name,Start,end
1,Alive,1,100
2,Sleep,10,25
3,Sleep,50,60
4,Sleep,70,81];
NoConcatenate

Sleep:
load Sno,'Alive'as Name,(end)+1 as Start resident Base where Name='Sleep';

load Sno-1 as Sno,'Alive'as Name,(Start)-1 as End resident Base where Name='Sleep';
drop table Base;

later you try to concatenate with original table

[rob_vander]

@ChannaK  This is helpful but not quite working fully.  I can't do Sno-1 because it's just the example. Actually there is no such column in data.

[Kushal_Chawda · Accepted solution]

@rob_vander  try below

Data:
Load * Inline [
Row	state	start_seq	end_seq
1	Alive	1	100
2	Sleep	10	25
3	Sleep	50	60
4	Sleep	70	81 ] (delimiter is '\t');

max_seq:
Load max(FieldValue('end_seq',RecNo())) as max_seq
AutoGenerate FieldValueCount('end_seq');

let vMax_Seq = Peek('max_seq');

Drop Table max_seq;

Final:
Load state,
     start_seq,
     if(state='Alive',Peek(start_seq)-1 ,end_seq) as end_seq,
     end_seq+1 as start_seq2,
     alt(Peek(start_seq)-1,$(vMax_Seq)) as end_seq2
Resident Data
Order by start_seq desc;

Drop Table Data;

Concatenate(Final)
Load 'Alive' as state,
      start_seq2 as start_seq,
      end_seq2 as end_seq
Resident Final
where state='Sleep';

Drop Fields start_seq2,end_seq2;