Match records from some data file

Hi All,

Since I’m new user of Knime and while using the same got stuck in processing. I have one file (Name as “Sub File”) where I have unique ID as Subscriber ID which I need to match from Invoice file of Apr’20 and get a new column in the “Sub File” as Apr-20. Can this be done in Knime? If yes can anyone help me with the work flow.
I’m sharing both the files. Please find the same attached.Sub File.xlsx (8.6 KB) Sample Apr-20 Data File.xlsx (9.4 KB)

It’s unclear to me what you’re trying to accomplish.

I have one file (Name as “Sub File”) where I have unique ID as Subscriber ID

You say this, but I see duplicate values for Subscriber ID.

I need to match from Invoice file of Apr’20 and get a new column in the “Sub File” as Apr-20.

What data is supposed to be in this new column?

The ID is reflecting duplicate as the Dealer was changed for the particular Subscriber, you can ignore for any one.

I want the Total Value of Invoice amount in the new column for the particular Subscriber ID.

One approach is to first use a GroupBy node on your Apr 20 data file, grouping on Subscriber ID and aggregating the Total column using Sum. This will give you a total for each subscriber.

If you then want to filter this list of subscribers, you can use the Reference Row Filter node. Here you would specify a column containing the subscriber IDs you want to keep (or discard).

Hi!!

Thanks it’s working. Only issue I’m facing that post using the Reference Row Filter the matched rows are reflecting, is there any option where I can keep both the matched or unmatched rows.

Since, I have to do the same process for many months to update the sheet.

I don’t quite understand what you mean by “reflecting”. I also don’t understand what output you’re asking for when you say you want to “keep both the matched or unmatched rows”.

Could you explain it a little more?

The original data is unchanged and always available from previous nodes.

Hello @BISHWAJIT,

maybe you can use Reference Row Splitter node instead of filter?

Br,
Ivan

Hi ipazin,

Thanks it resolves my query and solution received

1 Like

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