1

I am given a table like the following of a attendece of employees at a company. The data should be extracted from a sequenctial file which has comma seperated values.

ID Weekday Attendence
1 Monday Yes
2 Monday Yes
3 Monday No
4 Monday Yes
1 Tuesday Yes
2 Tuesday No
3 Tuesday Yes
4 Tuesday Yes

ID column has the employee ID and Weekday and Attendence columns has the exact meaning. I wan to count how many days the employee has shown up and I want the Days he attended as a comma seperated list. Like below

ID Weekday_List Attendenc_Count
1 Monday,Tuesday 2
2 Monday 1
3 Tuesday 1
4 Monday,Tuesday 2
Sahan Aloka Mendis
  • 23
  • 8
  • I did this using the sql when the data is on a database using the SQL db2 "select id, substr(listagg(weekday,','),1,60) weekdays, count(attendence) daynumber from rantab where Attendence='Yes' group by id" but I have no idea how to do this using an aggregator or a transformer stage. – Sahan Aloka Mendis Apr 10 '23 at 05:56
  • The aggregator stage is not much of use to aggregate the list as there is no string function that can be used with the aggregator stage. – Sahan Aloka Mendis Apr 10 '23 at 12:37

1 Answers1

1

It can be done with a transformer stage as concat aggregation is not supported by the aggregator stage.

You could use stage variables to do it or a transformer loop. The loop example shown here can be changed to do a concat.

If you decide to try it without the loop you need:

  • sorted and partitioned input to the transformer
  • use LastRowInGroup() to reset the string after each ID (and a second stage variable)
MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17