Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Calculate the difference between two identical strems

  1. #1
    Join Date
    Jan 2016
    Posts
    8

    Question Calculate the difference between two identical strems

    Hi,

    I have two identical strems and I would like to get the difference between these two streams. Below is the statement that I use in the database, however I would like to do this before inserting the data into the database.

    SELECT
    a.Col1,
    a.Col2,
    a.Col3,
    a.SalesEur - b.SalesEur
    FROM dbo.tableA a
    LEFT JOIN dbo.TableB b ON( a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND a.Col3 = b.Col3)

  2. #2
    Join Date
    Jan 2016
    Posts
    8

    Default

    Hi,

    I found a way. If there is a better way please let me know.

    1. I added a constant to identify the streams
    2. I appended the streams
    3. I used a row denormaliser to create two fields based on the key added in step 1.
    4. I used calculator to calculate the difference
    5. I used Select Values to remove extra fields

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Why not sort the rows on the key columns, and then use "Merge Rows"

  4. #4
    Join Date
    Jan 2016
    Posts
    8

    Default

    I tried different merge options but but I was not able to figure out how to get the actual difference. The Merge Rows (diff) step shows if the streams are not equal but I need to now how much is the difference between the streams.

    If this is possible could you give me some advise how to accomplish this?

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Sorry, I totally misunderstood your request.
    I would then use either the stream lookup (to get a.SalesEur in the b stream) or a Join statement, and then use a calculator step to subtract the two values.

  6. #6
    Join Date
    Jan 2016
    Posts
    8

    Default

    Hi,

    I used the stream lookup and that worked great! Thanks a lot for your help!

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.