Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Flag

  1. #1
    Join Date
    Jul 2012
    Posts
    200

    Default Flag

    Hi,

    How to identify if the row is deleted in the source table when we are implementing type 2 can anyone explain me ?

    My source src_emp example is :
    empno,ename,sal
    1,aaaa,100
    2,bbbb,200
    3,cccc,300
    3,cccc,400

    My target trg_emp table should look like
    empkey,empno,ename,sal,version,active_flag,deleted_flag
    1, 1, aaaa, 100,1, Y, N
    2, 2, bbbb, 200,1, Y, N
    3, 3, cccc, 300,1, N, N
    4, 3, cccc, 400,2, Y, N

    After doing this i will delete one row in source table which is sal like 300 now the deleted_flag in target has to become Y for 3rd row..
    How to approach for this..

    My final target table trg_emp should look like
    empkey,empno,ename,sal,version,active_flag,deleted_flag
    1, 1, aaaa,100, 1, Y, N
    2, 2, bbbb,200, 1, Y, N
    3, 3, cccc, 300,1, N, Y
    4, 3, cccc,400, 2, Y, N

    Please explain me how to resolve this?
    Last edited by yvkumar; 06-06-2013 at 06:08 AM.

  2. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    You need to join your target table to your data source in the input stream before you update the dimension. If both sources are tables in the same database, then this type of query should work (I use Postgres, so any DBMS-specific syntax belongs to it):

    Code:
    select
    coalesce(s.empno, t.empno) as empno,
    coalesce(s.ename, t.ename) as ename,
    coalesce(s.sal, t.sal) as sal,
    (case when s.empno is null then 'N' else 'Y' end)::varchar(1) as active_flag,
    (case when s.empno is null then 'Y' else 'N' end)::varchar(1) as deleted_flag
    from
    trg_emp as t full outer join src_emp as s
    on t.empno = s.empno
    Use that as your Table Input step and feed that to the step that updates the dimension.

    If your source is not a table in the same database as the target, you will have to use another method.

  3. #3
    Join Date
    Jul 2012
    Posts
    200

    Default

    My source is like flat file...

    Quote Originally Posted by robj View Post
    You need to join your target table to your data source in the input stream before you update the dimension. If both sources are tables in the same database, then this type of query should work (I use Postgres, so any DBMS-specific syntax belongs to it):

    Code:
    select
    coalesce(s.empno, t.empno) as empno,
    coalesce(s.ename, t.ename) as ename,
    coalesce(s.sal, t.sal) as sal,
    (case when s.empno is null then 'N' else 'Y' end)::varchar(1) as active_flag,
    (case when s.empno is null then 'Y' else 'N' end)::varchar(1) as deleted_flag
    from
    trg_emp as t full outer join src_emp as s
    on t.empno = s.empno
    Use that as your Table Input step and feed that to the step that updates the dimension.

    If your source is not a table in the same database as the target, you will have to use another method.

  4. #4
    Join Date
    Jul 2012
    Posts
    200

    Default

    Quote Originally Posted by yvkumar View Post
    My source is like flat file...
    How to do it for version in the query if there is any change? How to increment the version number compare to src_sal and trg_sal

    Can anyone guide me how to fix this problem using query?
    Last edited by yvkumar; 06-06-2013 at 05:17 AM.

  5. #5
    Join Date
    Jul 2012
    Posts
    200

    Default

    No its not working properly.. I have tried in all scnearios sometimes active_flag wont work and sometimes delete_flag wont works

  6. #6

    Default

    you might want to go in the other direction as a "second pass" (do your insert/update stuff as you do not in transformation1, then do the below stuff intransformation2)

    you can do this with "stream lookup" like this:
    * select everything from "tgt" table
    * select everything from the "src" flat file
    * use the "stream lookup" step
    ** if you find your record, do nothing
    ** if you DONT find your record, set your "deleted flag" to "Y"

  7. #7
    Join Date
    Jul 2009
    Posts
    476

    Default

    Since your source is a flat file, you might want to use the "Merge Rows (diff)" step, documented here: http://wiki.pentaho.com/display/EAI/Merge+rows. Create two input sources: a Table Input step for your trg_emp table, and a text file input step for your flat file. Ensure that both sources are sorted on the key (empno). You should be able to use an order by clause in the Table Input step, but you might have to add a sort step after the text file input step. Join those two streams with the Merge Rows (diff) step, and process the difference output according to your needs.

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.