Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Calculate field changes within a day

  1. #1
    Join Date
    Oct 2017
    Posts
    7

    Default Calculate field changes within a day

    Good day, I am pretty new in PDI and faceing small problem. But I am sure there is a simple solution. Which steps should I use to calculate field changes within a day? For example:

    ticket queue updated
    123 1 2017/01/31 16:16:00
    123 2 2017/01/31 17:00:20
    123 3 2017/01/31 17:01:20
    123 1 2017/02/02 17:01:20
    123 3 2017/02/02 23:01:20
    123 1 2017/02/15 17:01:20
    124 1 2017/02/15 17:01:20


    I want to get how many times (whithin an hour) was the queue switch:
    ticket queue_switch updated_time
    123 2 20170131
    123 1 20170202
    123 0 20170215
    124 0 20170215

    Can any one please help?
    sample.ktr

  2. #2
    Join Date
    Nov 2013
    Posts
    382

    Default

    If the data comes from a database table I would simply use something on the line

    select ticket,count(*)-1 queue_switch,cast(updated as data) as updated_time
    from mytable
    group by ticket,cast(updated as data)

    If the data comes from other sources, use Group By step to do exactly the same operation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.