Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: How to get rows that doesn't match witch streamlookup?

  1. #1

    Default How to get rows that doesn't match witch streamlookup?

    Here is the situation :

    • file_1 : XLS with 3 columns (colA, colB, colC)
    • file_2 : TXT with 1 column (col1)


    I use a streamlookup to match colA with col1 and it works find.
    But I also need to keep rows without matching in another "log file".

    How can I do it?
    Last edited by jcachico; 02-03-2014 at 07:35 PM.
    pdi-ce-4.4.0-stable
    jdk1.7.0_09
    Intel Core I3-2330M@2.2Ghz - 8Go ram
    Windows 7 FEP SP1

  2. #2
    Join Date
    Oct 2007
    Posts
    107

    Default

    You can test for nullability on your lookup key after the stream lookup.
    Name:  StreamLookup.png
Views: 21
Size:  21.7 KB

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by jcachico View Post
    How can I do it?
    You always get the same answer, it seems
    So long, and thanks for all the fish.

  4. #4

    Default

    Sorry, my fault!

    The thing is that I don't see this "null return value" that you are talking about :$

    Name:  screenshot.336.jpg
Views: 21
Size:  9.7 KB

    Name:  screenshot.337.jpg
Views: 21
Size:  19.4 KB

    Name:  screenshot.338.jpg
Views: 21
Size:  33.6 KB
    pdi-ce-4.4.0-stable
    jdk1.7.0_09
    Intel Core I3-2330M@2.2Ghz - 8Go ram
    Windows 7 FEP SP1

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Is it possible for you to attach your transformation with a couple of sample rows?
    Could speed things up quite a bit.
    So long, and thanks for all the fish.

  6. #6

    Default

    You're right, there it is

    pdiTest.zip
    pdi-ce-4.4.0-stable
    jdk1.7.0_09
    Intel Core I3-2330M@2.2Ghz - 8Go ram
    Windows 7 FEP SP1

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Technically, this looks alright at first glance.
    If you really want to filter for "Nome IS NOT NULL" you should do it immediately after the main input step.
    Are you sure you don't want to filter for "Nom IS NOT NULL" ?
    That would make much more sense to me.
    So long, and thanks for all the fish.

  8. #8

    Default

    I just try it with "nom" instead of "nome".

    But it's the same result, "OK" file got "martin" (wich is exactly want I want) but "KO" file is empty (instead of having "durant" and "dupont").
    pdi-ce-4.4.0-stable
    jdk1.7.0_09
    Intel Core I3-2330M@2.2Ghz - 8Go ram
    Windows 7 FEP SP1

  9. #9
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by jcachico View Post
    "KO" file is empty (instead of having "durant" and "dupont").
    Now I understand.

    You only will see the rows from the main stream coming out of Stream Lookup.
    If you want more you will have to use Merge Join.
    So long, and thanks for all the fish.

  10. #10

    Default

    Sorry but I don't get it.

    I must use "mergejoin" instead of "streamlookup" or with "streamlookup"?

    If it is with "streamloopkup", how must I do it?
    I try to use "merge" just after "stream" (see copy screen) but KO is still empty...

    Name:  screenshot.339.jpg
Views: 20
Size:  14.4 KB
    pdi-ce-4.4.0-stable
    jdk1.7.0_09
    Intel Core I3-2330M@2.2Ghz - 8Go ram
    Windows 7 FEP SP1

  11. #11
    Join Date
    Apr 2009
    Posts
    337

    Default

    If you use stream lookup, the fields from "Administrateur" will not flow downstream after the lookup.. if you use a merge join.. you can do a left outer join,.. and filter on the column which might return null.. Merge should not be used after stream.. it should be used as a replacement for stream..
    Regards,
    Madhu

  12. #12
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Sacre bleu, what if input and info stream of the Stream Lookup step were just interchanged?
    Attached Files Attached Files
    So long, and thanks for all the fish.

  13. #13

    Default

    With a little (big) help I finally reach the goal of my life (at least this morning one)

    So thank you very much to madhupenta and marabu for their precious help.

    If you, kind reader, have the same kind of problem, use the "mergejoin.ktr" file made by marabu.

    Once again "merci beaucoup".

    Jo.
    pdi-ce-4.4.0-stable
    jdk1.7.0_09
    Intel Core I3-2330M@2.2Ghz - 8Go ram
    Windows 7 FEP SP1

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.