topic Re: Sequence generation in Talend Studio

Sequence generation

lmit — Wed, 26 Oct 2022 10:08:41 GMT

Hi all,

I have requirement where I have to generate sequence in descending order

lets say have input like

id,name

1,a

2,b

3,c

1,d

1,e

2,f

my output should be like

id,name,seq

1,a,3

1,d,2

1,e,1

2,b,2

2,f,1

3,c,1

i know we can use sequence method which gives acs order but I want in desc order

any help would appreciate

thanks,

lmit

Re: Sequence generation

Anonymous — Wed, 26 Oct 2022 10:54:21 GMT

After you use the sequence function to generate a sequence id for each row, sort the rows by id and sequence_id column using tSortRow.

Re: Sequence generation

lmit — Wed, 26 Oct 2022 11:14:12 GMT

Hi @Shicong Hong

thanks for your reply , sort is working but I want the results in a separate column

id,name,seq,order

1,a,1,3

thanks,

lmit

Re: Sequence generation

Anonymous — Thu, 27 Oct 2022 03:10:47 GMT

You need a more step, do an inner join between two outputs.

one output:

id,name,seq

another output:

id,seq-->sort rows by id and sequence_id in desc order-->id,order

Do an inner join between these two outputs based on id column, and has the output table:

id,name,seq,order

Re: Sequence generation

lmit — Thu, 27 Oct 2022 18:03:20 GMT

Hi @Shicong Hong ,

thanks for your response , but when am doing inner join with unique match getting only one seq like

id;name;seq;order

1;a;1;3

1;b;2;3

and if I do all matches getting duplicates and with duplicates after duplicates I used tunqirow to remove the duplicates but still not getting results as I want

Can you please elaborate on the below step you mentioned

another output:

id,seq-->sort rows by id and sequence_id in desc order-->id,order

you mean to sort the rows with id and seq in desc order ?

can you please help here

thanks,

lmit

Re: Sequence generation

Anonymous — Fri, 28 Oct 2022 03:01:20 GMT

sorry, it might be a wrong job design in my previous post, I have created an example Job and test it, it has the output:

In this scenario, I need to group the id column and calculate the total number of each group, then generate a sequence id in desc order for each id group, for details, please see the screenshots.

Please try and let me know if you have any questions.

Regards

Shong

Re: Sequence generation

lmit — Tue, 15 Nov 2022 08:42:39 GMT

Thanks @Shicong Hong , it worked

Re: Sequence generation

Anonymous — Tue, 15 Nov 2022 08:51:51 GMT

great, thanks for your feedback!