Need help on grouping the records and compare against other groups

Sai2k19

I have a dataset with 2 columns PSR,TASK_TYPE. Here PSR is unique numbers. But there are many task types which can be repeated. Now I have applied a group by using aggregate operator. Now I want compare the PSR's among the task types, I don't want same PSR to be in all the task types. I just need that PSR be only in task type but not in all.

For example: If I have got a dataset as mentioned below.

PSR Task_Type

123  new

123  new

123  old

123  process

123  process

456  process

After applying group by result is

PSR  Task_Type

123  new

123  old

123  process

456  process

Now I need a logic like if PSR is present in old, new, process flag should show as new record which is trans only. If a PSR is present in only process task_type then flag it as service with the same task_type (process). 

End result should be like:

PSR  Task_Type Flag

123  new       trans

456  process   service

kayman

You might try with a double aggregation?

First aggregation -> group on PSR and TaskType
Second Aggregation -> group on PSR and count TaskType
If count(TaskType) > 1 then  Flag PSR as Trans, otherwise as NotTrans

Now you take this set, join it with your original using PSR as join field and replace the NotTrans Flag with taskType

Bit hard to explain but I hope you get the logic :-)

kayman

You might try with a double aggregation?

First aggregation -> group on PSR and TaskType
Second Aggregation -> group on PSR and count TaskType
If count(TaskType) > 1 then  Flag PSR as Trans, otherwise as NotTrans

Now you take this set, join it with your original using PSR as join field and replace the NotTrans Flag with taskType

Bit hard to explain but I hope you get the logic :-)

Sai2k19

Thanks. I understand what you are saying. And yes it solved my problem.