Transforming and Summing Data

[MikeBalzer]

Hello, 

I am looking for a way to match my data, the way the database was constructed makes it difficult. I am trying to do this without changing the script unless I have to. 

I am trying to match Table1 with Table2 data by transaction number which occurs multiple times in table 2 more times than not. 

I've attached a sample file for reference. 

[AshutoshBhumkar]

Hi, Its good to have a table like you mentioned instead of Table 2.

But you still can break the table into multiple tables and create a single table by concatenating them.

E.g

Table1:

Load * Inline [
Account Number, TransactionNo, Charge Amount, Product
1, 1111, $120.00, Driver
1, 1112, $70.00, 3 Wood
1, 1113, $100.00, Putter
1, 1114, $85.00, Wedge
2, 1115, $30.00, Driver
2, 1116, $60.00, 3 Wood
2, 1117, $90.00, Putter
2, 1118, $120.00, Wedge
3, 1119, $55.00, Driver
3, 1120, $25.00, 3 Wood
3, 1121, $25.00, Putter
4, 1122, $95.00, Wedge

];

Table2:
Load * Inline [
TransactionNo, PaymentAmount
1111, $60.00
1115, $30.00
1119, $55.00
1111, $60.00
];
Concatenate(Table2)
Load * Inline [
TransactionNo, PaymentAmount
1112, $35.00
1116, $60.00
1120, $25.00
1112, $35.00

];
Concatenate(Table2)
Load * Inline [
TransactionNo, PaymentAmount
1113, $50.00
1117, $90.00
1121, $25.00
1113, $50.00
];
Concatenate(Table2)
Load * Inline [
TransactionNo, PaymentAmount
1114, $55.00
1118, $120.00
1122, $95.00
1114, $30.00

];

Output:

Thanks,

Ashutosh

[MikeBalzer]

Thanks for the reply Ashutosh- 

I am a little confused, In my actual dataset I am loading thousands of rows of data, Do I use the same script to line up the multiple columns into one? 

[AshutoshBhumkar]

Hi @MikeBalzer ,

Yes. If the Amount columns are fixed to 4 then you can split the table into 4 tables with same column name and concatenate. Rows count will not be an issue.

Thanks,

Ashutosh