Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Unclear on results from Combination Lookup

  1. #1

    Default Unclear on results from Combination Lookup

    Hi Matt,

    First thing, I realise that it creates Junk dimensions

    However, it seems to be acting differently to what I would expect. If I create a Combination Lookup with 1 field and a technical key, then all is well. If I create a Combination Lookup with 2 fields (in this case, they are simply a mirror of each other e.g. (field1,field1)), then it works well on a run through on initial data. However, when I run that same transform on the same data it dies with an error that it is trying to insert a duplicate entry in the index?

    From my understanding, being Junk or otherwise, it should compare the two fields to the incoming record and if identical, then return the technical key. Why then is it trying to insert a record again, or have I missed something here?

    Cheers
    Jonathon

  2. #2
    Join Date
    May 2006
    Posts
    138

    Default RE: Unclear on results from Combination Lookup

    Why would you want to store mirror columns in a table?

    Attach your transformation, or a scaled down version of it which still has the problem and I will have a look at it. I'm curious.

    Regards,
    Sven

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Unclear on results from Combination Lookup

    Indeed. Or how about a drastical shocking solution: if it complains about duplicate fields, rename them. :-)
    But like Sven said, it's difficult to image a use-case for 2 identical fields with the same content and name in a database table...

  4. #4

    Default RE: Unclear on results from Combination Lookup

    The situation is quite simple at the moment, the table layout is of the form (id, code, name). The main issue is that at this point in the transform/time, the code is not being used (NULL if you like). Instead, I simply define both fields to take on the value of the 'name' field by using the same stream field value for both table field names. Is this not something that can (or should) be done?

    Just so it is clear, 2 identical fields for content, but different names

    Jonathon

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Unclear on results from Combination Lookup

    Well, again Jonathon, if it complains about it, just change the field names, why don't you?
    Put a Select Values in front of it, OK?

    Hey, I'm still waiting for an invitation to come over to Australia to fix all your problems.
    How about it?

    Oh, I'm sure Sven would love to come along as well.

    ;-)

    Matt

  6. #6
    Join Date
    May 2006
    Posts
    138

    Default RE: Unclear on results from Combination Lookup

    Jonathan,

    I just tried a small case and it works for me. Can you post a scaled down version of your problem... I would suggest "text file input" to "Combination Lookup".

    If I can't get your problem similuated, I can't help you.

    Sven

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Unclear on results from Combination Lookup

    Well Sven, don't spoil this, I would rather get a couple of plane tickets :-)

  8. #8

    Default RE: Unclear on results from Combination Lookup

    Hi Matt,Sven,

    OK, my fault here

    Although you still don't really get the scenario based on your postings, I found out what was causing the problem. The issue was that the two fields are of different size e.g. one is varchar(255) and the other char(8). This meant that a substring of the first field was stored in the second and based on the SQL query, this returned no match against the whole string which was being compared. Therefor it tried to insert a new record and then complained when the 255 length field had a duplicate entry.

    Simple solution was to make the fields identical in length, but not a good long term solution. Instead, I will probably implement it in triggers, or add a step to calculate the substr necessary before the combination lookup.

    Matt, as for the trip to Australia, if I can't get the performance of this above 2-300 records/second, I may have to take you up on the offer.

    Jonathon

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.