Combinatorial analysis millions of lines generation

[lawrencetu]

Hello everyone,

I need some help to generate an archive with some conditions:

1. I have an unique code with 2 columns that can match up to three other codes, for example:
   Unique Code | EAN
   VX0004 | 123
   VX0004 | 456
   VX0004 | 789
2. These Unique Codes are associated to a store sell out in a determined day and with a certain value (4 columns). Example:
   Unique Code | CNPJ | Data | Valor
   VX0004 123456 27/03/2023 14,49

My main goal is to have something like this in the final output (3 columns):
EAN | CNPJ | Data |
123 | 123456 | 27/03/2023
456 | 123456 | 27/03/2023
789 | 123456 | 27/03/2023

Thanks in advance!

[Daniel_Weikert]

Hi I probably don’t get your question correctly as I looks like joining to tables together based on unique code.
I hope someone else can help
br

[marzukim]

hi @lawrencetu ,
my approach,

KNIME_combine_analysis.knwf (126.9 KB)

rgds

[lawrencetu]

Hi @markzukim!

First of all, thanks for the reply.

For some reason i can’t download Vlookup extension. Do u know how can I do that?

When i entered knime, it asked to download all extensions but it can’t conclude.

Thank you!

[takbb]

Hi @lawrencetu , Value Lookup is new in KNIME 5.1 so in earlier versions you will need to replace it with other nodes.

In this case, I believe the suitable replacement for 4.x would be duplicate row filter and joiner:

On the duplicate row filter, make it unique on uc

In the joiner, join on uc2 = uc, and return Matching and Left unmatched rows. This should then give the same output as the Value Lookup in @marzukim 's workflow.

@marzukim you might want to check that this would give the same results that your existing workflow gives

[marzukim]

thank you for clarifying the node replacements. i’ve just noticed today that they are not included in the base 4.7 extension hope our @lawrencetu will be able to run the workflow with your recommendation.

i’ve run the amended vlookup replacement with (duplicate and joiner nodes) and it works well. i hope it meets @lawrencetu 's expectations.

thank you again for your help, sifu @takbb . you are the true problem solver!

[lawrencetu]

Hey @marzukim and @takbb!

Am I doing right?

[takbb]

Hi @lawrencetu , I’ve not looked at the specifics of what the workflow does but from the message I’d say that the Table Row to Variable node needs to be configured to include “uc2”

[marzukim]

@lawrencetu, apologize for not attaching the revised version. please find it attached.
KNIME_combine_analysis_v2.knwf (97.6 KB)

rgds

[lawrencetu]

Thank you both @markzukim and @takbb!