How to group records and send first record of group to online file and same for rest

Eight Stars

How to group records and send first record of group to online file and same for rest

 Hi Folks,

 

I need to group the records coming in email column and send the first record of each group into one file and next second record of each group to another file and remaining records also same.

Ex : 

a@a.com

a@a.com

b@a.com

b@a.com

a@a.com

c@c.com

 

As per above example first file should have 

a@a.com

b@a.com

c@c.com

second file

a@a.com

b@a.com

third file

a@a.com

 

Can anyone help me on this. Thanks in advance.

 

 

 

Thanks,

Bharath.


Accepted Solutions
Eight Stars

Re: How to group records and send first record of group to online file and same for rest

Thanks for your reply. We got the solution for this.

Even though we got we will also try which you said.

 

Thanks,

Bharath


All Replies
Nine Stars

Re: How to group records and send first record of group to online file and same for rest

Hi,

 

Please try below.

 

EmailSplit1.PNGEmailSplit2.PNGEmailSplit3.PNGEmailSplit4.PNGEmailSplit5.PNG

Regards,

Veeru Boppudi
Eight Stars

Re: How to group records and send first record of group to online file and same for rest

Thanks for your reply. We got the solution for this.

Even though we got we will also try which you said.

 

Thanks,

Bharath

Five Stars

Re: How to group records and send first record of group to online file and same for rest

@vboppudi

Hi I tried to use the below scenario and it seems the SEQ variable expression does not work. It produces and error.

Five Stars

Re: How to group records and send first record of group to online file and same for rest

@javvaji May I ask you to share your solution as i have a similar task to do.

Nine Stars

Re: How to group records and send first record of group to online file and same for rest

Hi Shaf,

 

Please provide your requirement if sample input and expected output.

 

Regards,

Veeru Boppudi
Five Stars

Re: How to group records and send first record of group to online file and same for rest

@vboppudi Thank you for your quick response.

I currently have a Excel macro enables workbook which reads the data in file then groups the data based on certain criteria, say for example Name and timesheet data. It then creates an individual workbook for each of these groups and names the workbook based on the name and the timesheet value column. It sorts the data first then inserts new line after each group, then copies this data and paste into another workbook, save and closes the file (current workbook), now read the next set of data in the original file and copies and paste the next set of group data and puts in new book and so on utill it reaches end of file with no data and closes the application.

I have been asked to rewrite this job in Talend as the timesheet data we longer receive in excel. The timesheet data comes in a pipe text file. I have been able to create a excel file from the pipe text file.

However, the problem I am having now is I need to read the output file which has been produced from the pipe text file and group this data and create a individual file for each set of group data. I have searched and cannot find any that meet my requirements. Please see screen shots.

 

 

So Once the pipe text file is converted to excel, data should like this. Currently already grouped. I this data is read, it needs to ignore  column 0= "I"

Column0

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

Column13

Column14

Column15

Column16

I

14001141

Testing

                                  

PO BOX

 

                                        

 

                                       

Manchester

 

GB11111111

Certified Tax Invoice   

5109341818

2019

 

18.06.2018

T

9827861

Blog

A

 

11.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

12.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

13.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

14.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

15.06.2018

111111

13

5

5

Hours

   

tester

A

18.06.2018

16.06.2018

35

I

14001141

Testing

                                  

PO BOX

 

                                       

 

                                        

Manchester

 

GB11111111

Certified Tax Invoice   

5109341818

2019

 

18.06.2018

T

9828186

Blog

B

 

11.06.2018

111111

1

7

1

Days

   

tester

A

18.06.2018

16.06.2018

4

T

9828186

Blog

B

 

13.06.2018

111111

1

7

1

Days

   

tester

A

18.06.2018

16.06.2018

4

T

9828186

Blog

B

 

14.06.2018

111111

1

7

1

Days

   

tester

A

18.06.2018

16.06.2018

4

T

9828186

Blog

B

 

15.06.2018

111111

1

7

1

Days

   

tester

A

18.06.2018

16.06.2018

4

T

9828186

Blog

B

 

12.06.2018

111111

1

7

0

Hours

Z003

tester

A

18.06.2018

16.06.2018

4

  I need talend to group the above as below. And based on this group produce a output for this first set of record. I need the job to loop through so that it produced new files for each group.

Column0

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

Column13

Column14

Column15

Column16

T

9827861

Blog

A

 

11.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

12.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

13.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

14.06.2018

111111

13

7.5

7.5

Hours

   

tester

A

18.06.2018

16.06.2018

35

T

9827861

Blog

A

 

15.06.2018

111111

13

5

5

Hours

   

tester

A

18.06.2018

16.06.2018

35

The excel output file should then save the file using the naming conventions of column 2 and column 15.

Nine Stars

Re: How to group records and send first record of group to online file and same for rest

Hi Shaf,

 

Please provide data in excel or CSV, i am not able to copy data.

 

Regards,

Veeru Boppudi
Five Stars

Re: How to group records and send first record of group to online file and same for rest

@vboppudi

Please see attached

Employee

Re: How to group records and send first record of group to online file and same for rest

Hi Shaf,

 

     You are posting duplicate query in another topic under Talend forum. Please stick to your topic so that attention will not be diverted.

 

https://community.talend.com/t5/Design-and-Development/group-records-and-create-file-for-each-record...

 

Warm Regards,

 

Nikhil Thampi

Five Stars

Re: How to group records and send first record of group to online file and same for rest

@nikhilthampi

 

Apologies, I saw this after I wrote my post and thought see if anyone can help.

Nine Stars

Re: How to group records and send first record of group to online file and same for rest

Hi Shaf,

 

Please try below job. I used limited attributes for process. 

Invoice1.PNG

 

Used tFilterRow to restrict "I" records.

Invoice2.PNG

Used tMap to restrict number of attributes from source.

Invoice3.PNG

And connected the tMap output to tFlowtoItterate.

Invoice4.PNG

connected tfolowtoiterate to tFxedFlow and created number of attributes

Invoice5.PNG

 

Then connected to output file.

Invoice6.PNGInvoice7.PNG

Regards,

Veeru Boppudi