Data input partitioning results

[heather (Heather Wilson)]

I created a a data input cube joined to a select statement on a “patient id”. the data input interaction collects 2 columns of data - patient id and date of a service.

I would like to be able to allow the end user to input new dates of service in the dashboard, attached to the patient id.

However, there ends up being more then one date per id, and I’d like to create row numbers partitioned by customer id so that I can just filter and select the top row / most recent date for each customer.

Can this be done with C# / a calculated element or formula on the metric set? I looked through a lot of the metric set configuration info, and it seems like there must be a way, but could not figure out the best way to do it.

I’d appreciate any help!

[Inactive-Member-21899052]

Hello Heather,

You can achieve this by including the auto created column CreatedTime column in your data input and then using the Sort Transform (https://www.dundas.com/Support/learning/documentation/data-metrics/transforms/other/sort) to sort the records by Created Date and Patient Id as seen below.

Then apply Fuzzy Grouping Transform (https://www.dundas.com/Support/learning/documentation/data-metrics/transforms/other/fuzzy-grouping)

Use Fuzzy Grouping to group and return the top value of the sorted results using Patient Id and a Probability Threshold as high as 0.9999. (Probability Threshold values are from 0.0001 to 1.0, I choose 0.9999 for an almost exact match for grouping together). This Fuzzy Grouping transform will return the last created record from the grouping.

Its best to do this at the Data cube level and not at the Metric set.

Regards.

[heather (Heather Wilson)]

This was exactly what I needed! Thank you! I love all the flexibility of working right in a cube… I think I’ll be using these transforms a lot more now!