Options

Need help on grouping the records and compare against other groups

Sai2k19Sai2k19 Member Posts: 2 Newbie
edited December 2019 in Help
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

Tagged:

Best Answer

  • Options
    kaymankayman Member Posts: 662 Unicorn
    Solution Accepted
    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 :-)

Answers

  • Options
    Sai2k19Sai2k19 Member Posts: 2 Newbie
    Thanks. I understand what you are saying. And yes it solved my problem.
Sign In or Register to comment.