Make groups based on starting point for new group

[pepe2209]

Hello

please look at the table below. I want to create te last column via the script.

ID	Newgroup	GroupTEMP	GroupCode
1	Yes	1	1
2			1
3			1
4	Yes	2	2
5	Yes	3	3
6			3
7			3
8			3
9			3
10	Yes	4	4

the table is ordered by ID. As long as Newgroup<>Yes then the GroupCode should be the last known GroupTEMP where Newgroup=Yes.

So how can i create this GroupCode in the script?

Your help would be much appreciated.

Regards,

Peter

[#2 · Accepted solution]

Hello Peter,

Please try below script:

Table1:
Load *, IF(WildMatch(NewGroup,'Yes')=1,GroupTEMP,Peek(GroupCode)) as GroupCode;
LOAD * INLINE [
ID, NewGroup, GroupTEMP
1, Yes, 1
2
3
4, Yes, 2
5, Yes, 3
6
7
8
9
10, Yes, 4
];

Output:

ID	NewGroup	GroupTEMP	GroupCode
1	Yes	1	1
2			1
3			1
4	Yes	2	2
5	Yes	3	3
6			3
7			3
8			3
9			3
10	Yes	4	4

[#3]

Hello Peter,

Is that script working as you expected.

Thanks,

Uday.

[pepe2209]

Hello Uday,

I just tested your script and it works perfect.

Thanks!