Loop and Join while filtering different row information

Hello @ all,

i have two tables with data. In one table are all the relevant customer information for a revenue calculation, but indeed i need the relevant price information from the other table to lead the calculation.
For one customer it is possible that there are more than 1 price event.

I would like to have my customer data table and with the joined price event columns for each price event if there is one for the customer.

Possibly it could look like this.

|Customer|Price Event 1|Date Event 1|Price Event 2|Date Event 2|Price Event 3|Date Event 3
|A|20|01.01.2019|-|-|-|-|-|-|
|B|25|01.01.2019|35|01.05.2019|-|-|-|-|
|C|||||||||
|D|||||||||
|E|||||||||

The relevant price event Table looks like this

|Customer|Date|Price|Counter|
|A|01.01.2019|20|1|
|B|01.01.2019|25|1|
|B|01.05.2019|35|2|
|C|01.01.2019|22|1|
|D|01.01.2019|50|1|
|D|01.03.2019|45|2|
|D|01.09.2019|60|3|
|D|01.01.2020|80|4|

Could someone help me and find a solution for me?

Best regards
Christopher

Hi @Christopher_Lozano and welcome back to KNIME Forum.

Use the Joiner to join tables based on customer ID and then use the Pivoting node, group on customer, pivot on counter and use “First” aggregation on date and price.

:blush:

3 Likes

Hi there @Christopher_Lozano,

alternatively you can use GroupBy on your second table with group column customer and List aggregation on price. Then use Cell Replacer to add this List column to first table followed with Split Collection Column :wink:

DM

Br,
Ivan

2 Likes

Thanks @ipazin for that quick answer.

I´ve tried it but it doesnt work with my workflow, because i have more than one column from the second price table, that i want to join to the data table.

I dont know but with the cell replacer i have my problems. It is getting stucked and dont join the column.

Maybe you have another solution.

Best regards.

Hi @Christopher_Lozano,

check @armingrudd solution then. It is reply before mine. It should work just fine.

Br,
Ivan

2 Likes

Thanks @armingrudd

That works exact the way i wanted.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.