Need to create unique group IDs in unbalanced data

[cengizeralp]

Hi everyone,

I have a data like below (black columns) and I need to create a new column called 'Group_ID' (blue column). Every group starts with 'TEST START' line. However usually ends with a 'Test_FINISH' line but this is not a rule as you can see from the data.

'TERMINAL_CODE' can not be used alone because some 'TERMINAL_CODE's contain several groups. 

SEQNO is a unique number for every line. 

I have found a solution with PREVIOUS function using in FOR ... NEXT loop. At every step of 'for ... next'  loop I get the group value of previous line. But it takes too much time to load data, because there are millions of lines of data and some groups have more than 15 lines

Does anyone have any idea how to resolve this issue easier?

Thanks a lot

cengiz

BARCODE	TERMINAL_CODE	SEQNO	TEST_NAME	TEST_START_TIME	Group_ID
80417910003759	T2	232.409.898	TEST START	10.03.2019 08:28:41	8
80417910003759	T2	232.409.923	PAT1	10.03.2019 08:28:45	8
80417910003759	T2	232.409.925	TD	10.03.2019 08:28:45	8
80417910003759	T2	232.409.927	Test_FINISH	10.03.2019 08:28:45	8
80417910003759	T3	232.396.994	TEST START	10.03.2019 07:57:23	9
80417910003759	T3	232.397.009	PAT2	10.03.2019 07:57:27	9
80417910003759	T3	232.397.010	TD	10.03.2019 07:57:28	9
80417910003759	T3	232.397.012	Test_FINISH	10.03.2019 07:57:28	9
80417910003759	T3	232.407.705	TEST START	10.03.2019 08:22:17	10
80417910003759	T3	232.407.732	PAT2	10.03.2019 08:22:22	10
80417910003759	T3	232.407.735	TD	10.03.2019 08:22:22	10
80417910003759	T3	232.407.737	Test_FINISH	10.03.2019 08:22:22	10
80417910003759	T3	232.410.067	TEST START	10.03.2019 08:28:25	11
80417910003759	T3	232.410.095	PAT2	10.03.2019 08:28:30	11
80417910003759	T3	232.410.096	TD	10.03.2019 08:28:30	11
80417910003759	T3	232.410.097	Test_FINISH	10.03.2019 08:28:30	11
80417910003759	T4	232.397.129	TEST START	10.03.2019 07:59:57	12
80417910003759	T4	232.397.167	BAL	10.03.2019 08:00:05	12
80417910003759	T4	232.397.168	TD	10.03.2019 08:00:05	12
80417910003759	T4	232.397.170	Test_FINISH	10.03.2019 08:00:05	12
80417910003759	T4	232.408.010	TEST START	10.03.2019 08:25:17	13
80417910003759	T4	232.408.090	BAL	10.03.2019 08:25:30	13
80417910003759	T4	232.408.094	TD	10.03.2019 08:25:30	13
80417910003759	T4	232.408.097	Test_FINISH	10.03.2019 08:25:31	13
80417910003759	T4	232.410.284	TEST START	10.03.2019 08:30:13	14
80417910003759	T4	232.410.364	BAL	10.03.2019 08:30:25	14
80417910003759	T4	232.410.366	TD	10.03.2019 08:30:26	14
80417910003759	T4	232.410.367	Test_FINISH	10.03.2019 08:30:26	14
80417910003759	T5	232.397.415	TEST START	10.03.2019 08:00:18	15
80417910003759	T5	232.397.444	TEST START	10.03.2019 08:00:23	16
80417910003759	T5	232.408.332	TEST START	10.03.2019 08:25:33	17
80417910003759	T5	232.408.355	AOUT	10.03.2019 08:25:37	17
80417910003759	T5	232.408.363	TD	10.03.2019 08:25:38	17
80417910003759	T5	232.408.365	Test_FINISH	10.03.2019 08:25:38	17

[Kushal_Chawda]

How do you sort the data? Is it already sorted ? 

[cengizeralp]

Hi Kush,

Data has already been sorted as shown

Sort order is BARCODE, TERMINAL_CODE, SEQNO. All are ascending order.

Thanks

[Kushal_Chawda · Accepted solution]

you can do something like below

T1:
LOAD
    BARCODE,
    TERMINAL_CODE,
    SEQNO,
    TEST_NAME,
    If(lower(trim(TEST_NAME))='test start',1,
    If(lower(trim(TEST_NAME))='test_finish',2)) as TEST_FLAG,
    TEST_START_TIME
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

T2:
NoConcatenate
Load *,
     if(RowNo()=1,1, 
     if((IsNull(TEST_FLAG) and Previous(TEST_FLAG)=1) or TEST_FLAG=2,Peek(Group_ID),
     if((TEST_FLAG=1 and Previous(TEST_FLAG)=2) or (TEST_FLAG=1 and Previous(TEST_FLAG)=1),
     Peek(Group_ID)+1,Peek(Group_ID)))) as Group_ID
Resident T1
Order by BARCODE,
    TERMINAL_CODE,
    SEQNO;
  
Drop Table T1;

[rwunderlich · Accepted solution]

If data is already sorted and you want to increment Group_ID when TEST_NAME= 'TEST START', I think you only need to add this to your load statement:

if(TEST_NAME = 'TEST START'
  ,alt(peek('Group_ID')+1,1)
  ,peek('Group_ID')
) as Group_ID

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[cengizeralp]

I did a preliminary study and found that both suggestions solved my problem. I think Rob's suggestion offers a simpler solution.
Robe & Kush, thank you so much both of you separately.

cengiz