Convert two rows having data into 2 column data

[MadhavTR]

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

[OTrieger]

@MadhavTR 
Will the 2nd value in this table will always be the Amount  or the rows might alternate?

[MadhavTR]

It will always be in the amount.

[Kaustubh17]

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

[OTrieger]

@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 

[RJ_1988]

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.

[MadhavTR]

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