Difficulty Joining Tables

KNIME Analytics Platform
1

I have subscription sales data that I am working. Companies subscribe to a product which can consist of a number of different modules. I am tracking quarterly results, and therefore am looking to identify price movements each quarter customers are invoiced. The data is something like the below:
Prod_ID Mod_ID Inv_Yr Inv_Qtr Sub Yr Price
0 1 2013 1 2013 50
0 2 2013 1 2013 150
0 1 2014 1 2014 100
0 2 2014 1 2014 200
0 3 2014 1 2014 300
0 1 2015 2 2015 150
0 2 2015 2 2015 250

 

I want to compare and classify the price change of subscriptions across different subscription years. To do this, I duplicated the table, created a Prev Year column, and did a left outer join on Subscription Year to Prev Year. So far so good - I'll be able to identify that this customer was 'New Business' and invoiced in 2013Q1, and with some extra effort I can identify they 'Upsold' module 3 in 2014Q1. I can do a group by on the Product Level to identify the % price change for each customer in each quarter.

Prod_ID Mod_ID Inv_Yr Inv_Qtr Sub Yr Price Prev Yr Prev Price
0 1 2013 1 2013 50 2012 0
0 2 2013 1 2013 150 2012 0
0 1 2014 1 2014 100 2013 50
0 2 2014 1 2014 200 2013 150
0 3 2014 1 2014 300 2013 0
0 1 2015 2 2015 150 2014 100
0 2 2015 2 2015 250 2014 200

 

What I cannot do however is identify that they downsold module 3 again in 2015. I would like to add a row that looks something like this:

Prod_ID Mod_ID Inv_Yr Inv_Qtr Sub Yr Price Prev Yr Prev Price
0 3 2015 2 2015 0 2014 300

 

I haven't been able to solve this problem - there is no row signalling the customer paid nothing for module 3 in 2015, so it seems there is nothing to join to. The only thing I can think of is to do a join on Sub Yr and a created Next Year column, but it will not fit into the data table I've already created.... I hope I explained this well enough, let me know if there is any more explaining required. Please could you help me with a solution using the parameters I've already provided?

2

?

3

Adding a row is fairly easy - "Table Creator" node + "Concatenate" node should get you sorted. Alternatively you can perform an "OUTER JOIN" and get some missing values, which you can impute with the "Missing Value" node.

Hope this helps!

-E