locked
Count Frequency of Consecutive Specified Value RRS feed

  • Question

  • Hi all,

    I'm currently working with a fairly large data set and am having some issue with a formula that will return the maximum frequency of a specified value that appears consecutively within a row.

    For example, respondent ID might have data that look like (where the values under Q1 through Q10 are the response options they chose; 99 indicates a non-response/omit/skipped question)

    ID Q1    Q2   Q3   Q4   Q5   Q6  Q7   Q8   Q9   Q10                  Max of 0      Max of 1    Max of 2     #of Skips

    1    0      2      2     1     2     2     2     99    2     99                          1               1              3                  2

    I've attached a workbook that will hopefully clarify a little bit more what I'm trying to accomplish.

    Consecutive Frequency Count Workbook

    Saturday, January 19, 2019 7:11 PM

Answers

  • you can do like this

    ;With CTE
    AS
    (
    SELECT ID,Val,Grp,COUNT(*) AS Occurance
    FROM
    (
    SELECT ID,Val,Col,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY REPLACE(Col,'PFi','') * 1) - ROW_NUMBER() OVER (PARTITION BY ID,Val ORDER BY REPLACE(Col,'PFi','') * 1) AS Grp
    FROM Table
    UNPIVOT(Val FOR Col IN ([PFi1],[PFi2],[PFi3],[PFi4],[PFi5],[PFi6],[PFi7],[PFi8],[PFi9],[PFi10]))u
    )m
    GROUP BY ID,Val,Grp
    )
    
    SELECT ID,
    MAX(CASE WHEN Val = 0 THEN Occurance ELSE 0 END) AS [Max0inarow],
    MAX(CASE WHEN Val = 1 THEN Occurance ELSE 0 END) AS [Max1inarow],
    MAX(CASE WHEN Val = 2 THEN Occurance ELSE 0 END) AS [Max2inarow],
    MAX(CASE WHEN Val = 3 THEN Occurance ELSE 0 END) AS [Max3inarow],
    SUM(CASE WHEN Val = 99 THEN Occurance ELSE 0 END) AS [Skipped]
    FROM CTE
    GROUP BY ID



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 20, 2019 6:09 AM

All replies

  • you can do like this

    ;With CTE
    AS
    (
    SELECT ID,Val,Grp,COUNT(*) AS Occurance
    FROM
    (
    SELECT ID,Val,Col,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY REPLACE(Col,'PFi','') * 1) - ROW_NUMBER() OVER (PARTITION BY ID,Val ORDER BY REPLACE(Col,'PFi','') * 1) AS Grp
    FROM Table
    UNPIVOT(Val FOR Col IN ([PFi1],[PFi2],[PFi3],[PFi4],[PFi5],[PFi6],[PFi7],[PFi8],[PFi9],[PFi10]))u
    )m
    GROUP BY ID,Val,Grp
    )
    
    SELECT ID,
    MAX(CASE WHEN Val = 0 THEN Occurance ELSE 0 END) AS [Max0inarow],
    MAX(CASE WHEN Val = 1 THEN Occurance ELSE 0 END) AS [Max1inarow],
    MAX(CASE WHEN Val = 2 THEN Occurance ELSE 0 END) AS [Max2inarow],
    MAX(CASE WHEN Val = 3 THEN Occurance ELSE 0 END) AS [Max3inarow],
    SUM(CASE WHEN Val = 99 THEN Occurance ELSE 0 END) AS [Skipped]
    FROM CTE
    GROUP BY ID



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 20, 2019 6:09 AM
  • you can do like this

    ;With CTE
    AS
    (
    SELECT ID,Val,Grp,COUNT(*) AS Occurance
    FROM
    (
    SELECT ID,Val,Col,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY REPLACE(Col,'PFi','') * 1) - ROW_NUMBER() OVER (PARTITION BY ID,Val ORDER BY REPLACE(Col,'PFi','') * 1) AS Grp
    FROM Table
    UNPIVOT(Val FOR Col IN ([PFi1],[PFi2],[PFi3],[PFi4],[PFi5],[PFi6],[PFi7],[PFi8],[PFi9],[PFi10]))u
    )m
    GROUP BY ID,Val,Grp
    )
    
    SELECT ID,
    MAX(CASE WHEN Val = 0 THEN Occurance ELSE 0 END) AS [Max0inarow],
    MAX(CASE WHEN Val = 1 THEN Occurance ELSE 0 END) AS [Max1inarow],
    MAX(CASE WHEN Val = 2 THEN Occurance ELSE 0 END) AS [Max2inarow],
    MAX(CASE WHEN Val = 3 THEN Occurance ELSE 0 END) AS [Max3inarow],
    SUM(CASE WHEN Val = 99 THEN Occurance ELSE 0 END) AS [Skipped]
    FROM CTE
    GROUP BY ID



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thanks Visakh16! I'm not quite familiar with CTE. Would this solution be down in power query or powerpivot? Would you be able to post the workbook with the solution to get a better visual?

    Thanks!

    Sunday, January 20, 2019 3:33 PM
  • Darko, any progress on this one?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, April 5, 2019 6:29 AM