I want to join 2 datasets but one of them have values with comma

image

I want output table as

image

This is urgent pls.

Hello @maanvendra,

there are multiple ways to achieve this. One would be to use Cell Splitter on Input table 1 to get every value in single cell, then use Unpitoving node to have all values in one column, apply your logic using Joiner or Cell Replacer node and finish it with Pivoting node to get desired output.

Check above explained approach here:

Br,
Ivan

4 Likes

Can you please provide the workflow for it.
Im able to. use cell splitter to get grouped values to individual record and then join with 2nd table.

The problem is to again get those records to single row with comma separated.

Hi @maanvendra

Here’s another method which splits out the comma separated lists into individual rows by combination of Cell Splitter and ungroup. After joining, it then regroups based on a counter value which represents the original row that the values were grouped on.


joining comma separated data sets.knwf (20.4 KB)

5 Likes

The pace at which people reply is incredible :-).

Here’s my take at this:

4 Likes

Hi @MartinDDDD , your final output is not something im looking for it is giving the same output as input

@MartinDDDD :slight_smile: Just a tip: you can use option Skip rows containing missing cells in Unpivoting node so you don’t need Missing Value node.

@maanvendra he just used wrong column in GroupBy node. Replace it with column holding letters and you’ll get desired output.

Br,
Ivan

2 Likes

Ups - accidentally grouped the wrong column. Fixed and re-uploaded. Good pickup.

1 Like

Mind-blown for today :smiley: Thanks for the tip :slight_smile:

2 Likes

Thanks all for such a quick reply. Wonderfull community.

I find @MartinDDDD & @takbb, solutions to my liking.

Thanks again.

3 Likes

You’re welcome @maanvendra . There’s often a variety of approaches and great to see a few different ones here.

2 Likes

Hello @maanvendra
I’m late to the discussion, but alternatively; you can approach the replacement with ‘Dictionary Tagger’ node. I think it is a cleaner method for your use case as you won’t need to disaggregate or manipulate the text.

BR

2 Likes