topic Re: Need recommendation on best way to split dataset into two - one with repeating records and another with non repeating records in Talend Studio

Need recommendation on best way to split dataset into two - one with repeating records and another with non repeating records

sushantV — Wed, 22 Aug 2018 20:43:30 GMT

I need to figure a way to split a dataset into two datasets, based on the data repetition in one of the fields. If a value is getting repeated, I need to put all the rows in one dataset and if if it's unique, that row should go to another.

e.g. if I have the dataset with following records:

For the above dataset, the repeating records need to go to dataset 1 so that the dataset 1 contains

and non-repeating values need to go to another dataset so that the dataset 2 contains

If I use tUniqueRow, A1 would still go to the resultant dataset. I could make the unique dataset first, then do some sort of comparison with the remaining records and them add the row in the other dataset, remove the row from unique dataset. This is messy. Can anyone make a recommendation?

Re: Need recommendation on best way to split dataset into two - one with repeating records and another with non repeating records

TRF — Wed, 22 Aug 2018 21:27:57 GMT

You got it, tUniqRow + tMap to remove rows which exist as duplicates.

Re: Need recommendation on best way to split dataset into two - one with repeating records and another with non repeating records

sushantV — Fri, 24 Aug 2018 15:46:08 GMT

I tried but looks like I am stuck. With the tUniqRow, I can split my dataset into unique and repeating bits. However, the tMap component won't let me compare them both. For some unknown reason, I can't seem to add both them to the tMap mode. How do I do that? Unless I am able to draw a comparison between the two splits, I won't be able to move around the data so that all repeating records are separated in one segment, and non repeating in another. @TRF

Re: Need recommendation on best way to split dataset into two - one with repeating records and another with non repeating records

TRF — Fri, 24 Aug 2018 17:21:33 GMT

After tUniqRow catch uniq rows into a tHashOutput (here called uniq) and catch replicated rows into an other tHashOutput (here called replicate).
On the next subjob, use a tMap with "uniq" tHashInput for the main input and "replicate" for the lookup.
Join both using an inner join and add 2 outputs for the tMap, 1 catches the rejected inner join rows (really uniq) and the other don't catch them.
Redirect 1rst output to what you want and 2nd output to a tHashOutput linked to "replicate" (append mode is required).
This new tHashOutput should contain the subset of replicated records.
Hope you got it.

Re: Need recommendation on best way to split dataset into two - one with repeating records and another with non repeating records

sushantV — Fri, 24 Aug 2018 17:47:08 GMT

I am sure I got lost somewhere in the middle. Let me do some reading, before attempting this solution.