topic Create multiple output rows based on the input rows in Talend Studio

Create multiple output rows based on the input rows

Serphentelm — Fri, 15 Nov 2024 21:39:36 GMT

Hi, I need to create multiple output rows based on my input, here an example:

my input is as follow:

From------------- To------------ cod

2016-01-01 | 2016-05-31 | 1

2016-06-01 | 2016-09-30 | 1

2016-10-01 | 2016-11-30 | 1

2016-01-01 | null | 2

2016-01-01 | 2016-06-30 | 3

and my ouput should be composed from two tables:

one with only unique cod

out1:

cod

and a second table with all rows + 1 if there are a To field populated, as follow:

out2:

From------------- To------------ cod

2016-01-01 | 2016-05-31 | 1

2016-06-01 | 2016-09-30 | 1

2016-10-01 | 2016-11-30 | 1

2016-12-01 | null | 1

2016-01-01 | null | 2

2016-01-01 | 2016-06-30 | 3

2016-07-01 | null | 3

is there a way to achieve this?

Thanks in advance

Anonymous — Wed, 21 Jun 2023 10:42:05 GMT

I'd load the whole dataset into HsqlDB and write 2 queries that give you the needed output.

Using tMap you can split the dataflow.

But then you'd need to check if all the unique ID-s have a null value and if they don't then those should be appended.

So purely inside talend studio it would be really complicated and likely require a couple intermediate steps as well.

I'd choose the SQL approach in this case.

Serphentelm — Wed, 21 Jun 2023 12:07:15 GMT

Since I start with an sql table, I thought the same. Wuold be easier to recreate che ideal output via query, than having all the job done in Talend.

Asked this just to know if there was a way.

Thanks anyway