Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeBalzer
Contributor
Contributor

Transforming and Summing Data

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. 

Labels (1)
3 Replies
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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:

AshutoshBhumkar_0-1632938129988.png

Thanks,

Ashutosh

MikeBalzer
Contributor
Contributor
Author

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
Partner - Specialist
Partner - Specialist

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