Dynamicmethod to extract and sum numeric data embedded in string variable, given condition

[CTalteryx]

Hi there,

I'm having trouble processing this dataset. For each variable I need to sum the quantity (number before lowercase x) each variable occurs at, only when the number of days is 6 or higher - everything else is excluded. In theory there can be any number of lines of data under "Past:", any quantity (1x to 99x and theoretically upwards) of each variable occurring, and any number of days after "Aged", so i need a dynamic approach which is what's confusing me. The "Past Text" column changes from dataset to dataset that this will be applied to, but the rules remain the same. I've included dummy data below with all the possible rules included.

Does anyone have any ideas how i can get this to dynamically sum the quantity when the condition more than 5 Days Aged is met?

Thanks in advance for any ideas.

Var	Past Text	Desired Output
1	Past: 1x Aged 5 Days 1x Aged 6 Days 3x Aged 4 Days	1
2	Past: 3x Aged 3 & 11 & 16 Days 1x Aged 6 Days	3
3	Past: 3x Aged 6 & 3 & 9 Days 1x Aged 17 Days	3
4	Past: 4x Aged 3 Days	0
5	Past: 32x Aged 11 Days 2x Aged 3 & 6 Days	33

[usmanbashir]

@CTalteryx - please see attached workflow. I did note that your desired output for last record is 33 when I think it should be 32? Let me know otherwise. Hope this helps!

[CTalteryx]

Hi Usman, thanks for taking a look! I think this is close but doesn't quite solve it.

Maybe i didn't explain clearly enough, for record 5 the desired output is 33 as there are 32 occurrences aged 11 days, and then in the line below there are 2 occurrences, 1 aged 3 days (not counted) and one aged 6 days (counted), so 33. So for record 2 the output should also be 3 (as 11 16 and 6 days), and record 3 it should also be 3 (as 6 9 and 17 days). Also i would need Var 4 in the final output to display with a 0 still.

Do you think it is possible to build this in? I can't think how to separate the quantities in those embedded lines based on the age.

Thanks again!

[usmanbashir · Accepted solution]

@CTalteryx - I think that makes more sense! I attached a new workflow. Let me know if this what you were looking for!

[CTalteryx]

Yes that has done the trick! Thanks so much Usman, works perfectly against the checks I've done on the real data. Nice work :)