Search code examples
qliksenseqlik-expression

Count Number of Occurrences Before Specific Field Then Reset

In either the Data Load editor or a measure in a table in Qlik Sense I am trying to count the number of distinct results for each UserID before the Result = "Cat" occurs.

Source Table

UserID Date Result
A 2/17/22 Goat
A 2/18/22 Goat
A 2/19/22 Dog
A 2/20/22 Cat
A 2/21/22 Dog
A 2/22/22 Cat
B 2/19/22 Dog
B 2/20/22 Cat

Expected Result Table

UserID # Results Before Cat
A 1.5
B 1

Explanation UserID = A would be 1.5 because it is an average of 2 distinct results (Goat & Dog before Cat) then another occurrence of 1 Dog before Cat UserID = B would be 1 since there was only one distinct Result before Cat

Solution

  • Have a look at the annotated script below.

    Once ran the output will be:

    result

    table viewer

    // Load directly from SO
WebData:
Load
    // create increment per UserID
    if(UserID <> Peek(UserID), 1, peek(Increment) + 1) as Increment,
    RecNo() as Record,
    UserID,
    Date, 
    Result
From
    [https://stackoverflow.com/questions/78189164/count-number-of-occurrences-before-specific-field-then-reset]
    (html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1)
;

// per UserID find whish is the min Increment
// number for Cat records
MinIncrement:
Load
    min(Increment) as MinIncrement,
    UserID
Resident
    WebData
Where
    Result = 'Cat'
Group By
    UserID
;

// join back to the WebData
join (WebData) Load * Resident MinIncrement;


// find how many distinct Result values
// exists before the min Cat record
Temp1:
Load
    Count(distinct Result) as ResultCount,
    UserID
Where
    IncludeFlag = 1
Group By UserID
;
Load
    if(Increment < MinIncrement, 1, 0) as IncludeFlag,
    UserID,
    Result
Resident
    WebData
;

// join the MinIncrement value
// so we can have the record number
// of the Cat record (per UserID)
join

Load
    UserID,
    MinIncrement
Resident
    MinIncrement
;

// we dont need this table anymore
Drop Tables MinIncrement;

// calculate the final result
FinalResult:
Load
    UserID,
    (MinIncrement - 1) / ResultCount as FinalResult
Resident
    Temp1
;

// we dont need the following tables and fields anymore
Drop Table Temp1;
Drop Fields Increment, MinIncrement From WebData;