Reversing Rows
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
oh man i was literally at the crosstab back when you sent this, nicely done @atcodedog05 😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Was your approach same @TheOC 😅 ?
If your approach is different i would definitely recommend you to post it 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
