Reversing Rows

[peterg97]

Hi all, 

I need some advice on how to reverse the order of data in rows - I have attached an excel file with before and after for reference. I believe that the solution will involve transposing the data, giving it record ID's and sorting it in some way, but I just haven't found the correct combination yet, so would appreciate any help!

Cheers, 

Peter

[TheOC]

Edit: Realised i misinterpreted the data, giving it another go!

[atcodedog05 · Accepted solution]

Hi @peterg97 

Here is how you can do it.

1. Using transpose tool to convert year columns to rows.

2. Filter 0 values

3. 1 sort tool i am sorting row year by ascending and another i am sorting by descending. Reason is i need to change first to last and reverse order.

4. Next i am doing positional join. (You might need to cross check on this step whether you getting expected output). Since first sort was done on year of account key column it should match.

5. Crosstab back to table.

6. Using data cleanse tool replace nulls to 0's

Hope this helps 🙂

[TheOC]

oh man i was literally at the crosstab back when you sent this, nicely done @atcodedog05 😁

[atcodedog05]

Was your approach same @TheOC 😅 ?

If your approach is different i would definitely recommend you to post it 🙂

[TheOC]

pretty much yeah, I did try to do something fancy with the grouped record ID tool made by @mceleavey , but turns out this wasn't necessary!

[danilang]

Hi @peterg97 

Good solutions as always from @atcodedog05.  My question is "why do you need to do this"?  The reversing of the data completely changes the relationship between the rows and columns.  The original intersection of 2017/2017 was 17K and then it became 26K.   Was the original excel file incorrectly made? 

Dan

[peterg97]

Hi @atcodedog05, 

Cheers for this! Really helpful! In applying this to data sets, I have come across an issue - When the rightmost piece of data is zero, but needs to be zero, by filtering out any zero values, this piece of information is lost and the rows shuffle over. An example is the below input, where the 2017/2017 intersection is 0, and with the workflow this will result in the 2nd table below, where as I need the third table.

Input:

Workflow output:

Desired output:

Basically, all the data within the triangle is important, but I can see why it will be difficult to differentiate between which zeros should be included and which should be filtered out. 

Any help is appreciated, and thanks again for your help already!

[peterg97]

Hi @danilang, 

Good question, its for a university project to model the development of insurance claims, and yes the data just comes in that incorrect format, so we need to fix it so that it can be interpreted. We have a correct finished triangle and we need to manipulate the incorrect data to be the same as it. 

Peter

[atcodedog05 · Accepted solution]

Hi @peterg97 

To fit your scenario and your key triangle stuck a logic in my mind. What i was able to observe is 2016 has data for all columns >=2016 and for 2017 has data for all columns >=2017 so basically column year should >= rows year. I was able to implement my logic with a small change in workflow.

I have changed the filter tool to use the above logic instead of filtering zeros. [Name]>=[Year]. I am getting the expected output.

Workflow:

Please check and let me know

Hope this helps 🙂