Stephane Forrer Posted November 22, 2023 Share Posted November 22, 2023 Hi, I'm looking to create a running count of when a Patient takes DrugA and inserting a new column as a transformation in the data canvas. I suspect I need to use intersect but can't seem to get it working. What I currently have is Count([Treatment]) OVER ([Patient], [Treatment]) which I isn't correct.RunningCount is what I'm trying to calculate:Patient | Treatment | RunningCount1111 | DrugA | 11111 | DrugA | 21111 | DrugB | 1112 | DrugB | 1112 | DrugA | 11112 | DrugB |1113 | DrugA | 11114 | DrugA | 11114 | DrugA | 21114 | DrugA | 3Thanks!Stephane Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 22, 2023 Share Posted November 22, 2023 This worked for me with your example: case when Trim([Treatment])='DrugA' then DenseRank(RowId(),[Patient]) end Link to comment Share on other sites More sharing options...
Stephane Forrer Posted November 22, 2023 Author Share Posted November 22, 2023 Brilliant, thanks Gaia it works a treat.My actual data has gaps below so I've used Calculate & Replace column to fill down, unless you know of a way to do it a single expression? :)// Calculate & Replace column: "RunningCount" CASE WHEN LastValidBefore(Trim([Treatment]))="DrugA" THEN LastValidBefore([RunningCount])ENDUpdated Example Data: Patient|Treatment|Timepoint|RunningCount1111|DrugA|0|11111||1|11111|DrugA|0|21111||1|21111|DrugB|0|1111||1|1112|DrugB|1|1112||1|1112|DrugA|0|11112||2|11112|DrugB|0|1112||1|1113|DrugA|0|11113||1|11114|DrugA|0|11114||1|11114||2|11114|DrugA|0|21114||1|21114||2|21114|DrugA|0|31114||1|3Thanks Link to comment Share on other sites More sharing options...
Solution Gaia Paolini Posted November 22, 2023 Solution Share Posted November 22, 2023 Would this work: case when Trim([Treatment])='DrugA' then DenseRank(RowId(),[Patient]) when ([Treatment] is null) and (Trim(LastValidBefore([Treatment]))="DrugA") then DenseRank(RowId(),[Patient]) end I only used Trim(..) as in my case I had some spurious spaces in the drug name, you probably don't need it if the field does not have leading or trailing spaces. Link to comment Share on other sites More sharing options...
Stephane Forrer Posted November 23, 2023 Author Share Posted November 23, 2023 Yes this worked thank you! 👍 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now