I am analyzing insurance claims data. The table has a column with dates of the visits. I want to generate another column that shows the recurring visits for each member. The visits will be Continous + ordered based on the sequence of visits - e.g., Visit 1. Visit 2. Visit 3 as per the member. Hence let say I went to a doctor on Jan 1, Jan 10, Jan 30, and March 5, I want the script to generate the recurring sequence of visits as Visits 1, 2, 3, and 4 for every member in the database. i.e, I should be able to use the same sequence for each member based on the sequence of claim dates.
My ultimate goal is the then establish the variability of the cost of care with each recurring visits for members by diagnosis. It's a large data sent hence unable to do it manually.
I tried recurring analysis and that did not get me too far. I am sure there is a simple way to do this...
In the discussion, an alternative that did not require the sorting of the data prior to creating the Visit #. It is also a column format, which means, that running a separate script does not have to be done:
dt = Current Data Table();
theRow = Row();
theCustomer = :Customer ID;
seq = N Rows( dt << get rows where( :Customer ID == theCustomer & Row() < theRow ) ) + 1;
This will run slower that the sorting version, but it might be a better solution.