Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Need to create unique group IDs in unbalanced data

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

BARCODETERMINAL_CODESEQNOTEST_NAMETEST_START_TIMEGroup_ID
80417910003759T2232.409.898TEST START10.03.2019 08:28:418
80417910003759T2232.409.923PAT110.03.2019 08:28:458
80417910003759T2232.409.925TD10.03.2019 08:28:458
80417910003759T2232.409.927Test_FINISH10.03.2019 08:28:458
80417910003759T3232.396.994TEST START10.03.2019 07:57:239
80417910003759T3232.397.009PAT210.03.2019 07:57:279
80417910003759T3232.397.010TD10.03.2019 07:57:289
80417910003759T3232.397.012Test_FINISH10.03.2019 07:57:289
80417910003759T3232.407.705TEST START10.03.2019 08:22:1710
80417910003759T3232.407.732PAT210.03.2019 08:22:2210
80417910003759T3232.407.735TD10.03.2019 08:22:2210
80417910003759T3232.407.737Test_FINISH10.03.2019 08:22:2210
80417910003759T3232.410.067TEST START10.03.2019 08:28:2511
80417910003759T3232.410.095PAT210.03.2019 08:28:3011
80417910003759T3232.410.096TD10.03.2019 08:28:3011
80417910003759T3232.410.097Test_FINISH10.03.2019 08:28:3011
80417910003759T4232.397.129TEST START10.03.2019 07:59:5712
80417910003759T4232.397.167BAL10.03.2019 08:00:0512
80417910003759T4232.397.168TD10.03.2019 08:00:0512
80417910003759T4232.397.170Test_FINISH10.03.2019 08:00:0512
80417910003759T4232.408.010TEST START10.03.2019 08:25:1713
80417910003759T4232.408.090BAL10.03.2019 08:25:3013
80417910003759T4232.408.094TD10.03.2019 08:25:3013
80417910003759T4232.408.097Test_FINISH10.03.2019 08:25:3113
80417910003759T4232.410.284TEST START10.03.2019 08:30:1314
80417910003759T4232.410.364BAL10.03.2019 08:30:2514
80417910003759T4232.410.366TD10.03.2019 08:30:2614
80417910003759T4232.410.367Test_FINISH10.03.2019 08:30:2614
80417910003759T5232.397.415TEST START10.03.2019 08:00:1815
80417910003759T5232.397.444TEST START10.03.2019 08:00:2316
80417910003759T5232.408.332TEST START10.03.2019 08:25:3317
80417910003759T5232.408.355AOUT10.03.2019 08:25:3717
80417910003759T5232.408.363TD10.03.2019 08:25:3817
80417910003759T5232.408.365Test_FINISH10.03.2019 08:25:3817
Labels (2)
2 Solutions

Accepted Solutions
Highlighted
MVP
MVP

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;

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
Highlighted
MVP
MVP

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

Highlighted
Contributor III
Contributor III

Hi Kush,

Data has already been sorted as shown

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

Thanks

Highlighted
MVP
MVP

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;

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Contributor III
Contributor III

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