Convert two rows having data into 2 column data
- 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 have a transaction dataset in which amount is in line 1 and tax is in line 2. I want to bring this into 1 line with side by side column having amount and tax. The key is the invoice num (or reference in my case)
Input
Invoice num | tax code | USDamount |
123 | P1 | 100 |
123 | P1 | 1000 |
Expected output
invoice num | tax code | Amount | Tax |
123 | P1 | 1000 | 100 |
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@MadhavTR
Will the 2nd value in this table will always be the Amount or the rows might alternate?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It will always be in the amount.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @MadhavTR
Please find the attached SC of the desired solution:
- Sample the data in the text tool
- group by tax code, invoice number
- First, last the values.
- rename the field name to amount and tax
If this solution resolved your query, please mark it as solved to assist others with similar issues.
Best regards,
Kaustubh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@MadhavTR
In this way the most easiest way will be to use the Summarize Tool.
Group the data on Invoice Number and Tax code and Concatenate USAmount.
Then use Parse Text to Columns Tool and you will get the data in that manner.
What left is to rename the 2 fields, you can use Select tool to do that
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
1) Add one identifier Column called as 'Type' (it can be dynamic based on the pattern).
2) Use a cross Tab tool - Group Data by 'Invoice Number' and 'Tax Code' , change Column headers 'Type' and Values for new columns as 'USDAmount', method for aggregating values 'Sum', you will get the desired result.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Kaustubh,
Thanks for your reply last time. I have now got 2 extra usecases on top it
1.Can i add and summarize based on certain conditions (here gst code will always be present)
2. Also take data from a certain column where the gstcode is missing (this will be true always)
for example
Input
Invoice Num | Description | gstcode | USDAmount | TAXID |
123 | Tax | P1 | 10 | |
123 | Tax | P1 | 10 | |
123 | Amount | P1 | 100 | |
123 | Amount | P1 | 100 | |
123 | ABCD |
Output
Invoice Num | tax code | Amount | Tax | Tax ID |
123 | P1 | 200 | 20 | ABCD |
Thanks in advance
