How to combine two reports to create a wide data table?

Hi everyone I have a quick question.

I have a report that consists of product name, shipping dates, and volume for each date that the product was shipped. I have a second report that is just a fiscal calendar that shows fiscal month and week.

I want to be able to combine these two reports and create a table that shows the product name and the volume that was shipped out for each quarter.

Product Name Q1 Q2 Q3 Q4
Volume Volume Volume Volume

Hi @mtomas15 and welcome to the KNIME community forum

This sounds as a job for the -Joiner- node. Could you please upload here the two data tables or a workflow containing both to help you further ?

Best
Ael

1 Like

Hi guys and ladies,

A tip for it is using a “cell replacer” node. Joiners node can be used for it for different kind of selection, but if you just need add a column information that exists in anther table, cell replacer will do the job, like the excels’s nslookup/procv formula.

Sounds like you could use @aworker s proposal but need to create “similar” dates for the join first (Either Extract Datetime Node or with string manipulation)
br

I cannot use the joiner node because there are no common columns. I have dates on the first report and I have quarterly dates on the second report. I want to be able to match those dates on the first report and change them into quarters by using the second report.

In this case you could use a -Cross Joiner- node if the two tables are not too big and then use a -Rule-Base Row Filter- node to detect on the joined rows where the date is within the quarterly period.

Do you have somewhere I can reach out to you for more help and a more detailed explanation?

I’ll provide more of a more detailed explanation of what I’m trying to accomplish.

The goal is to be able to create a table that shows how much volume a product does for each quarter from Q1-Q4.

I have 1 report that shows the name of the product, the date of the product, and the quantity of the product.

The second report shows a fiscal calendar. For example, if the calendar date is 07/30/2022 then the fiscal year quarter is 2023Q1.

I want to be able to change the date of the first report into quarters by following the calendar on the second report. For example, if the date of the product(first report) is 11/30/2022. I want to be able to change that to Q2 by following the dates on the other report that show the fiscal calendar.

Then after changing the dates on the first report to quarters by following the fiscal calendar I want to create a table that shows the Total volume done for each product by each quarter.

Hi @mtomas15

I guess you are asking the question above because your data might be confidential or cannot be shared here in the forum ? In this case, I would suggest to create a couple of dummy table examples that look like your real data so that I or some other KNIME community volunteers can help you all together. What do you think ?

Looking forward to help you further.

Kind regards,
Ael