Hitachi Vantara Pentaho Community Forums
Results 1 to 17 of 17

Thread: Leading Zero's

  1. #1
    Join Date
    May 2007
    Posts
    8

    Default Leading Zero's

    After upgrading towards 3.0 GA I only receive values with leading zero's. the data type is INT. Can anyone help me?

    Grtz,

    Harry

  2. #2

    Default

    Hi,

    I can't help you with whats changed since the upgrade but if you put a select values step after the point where the data is selected and set the length of your int field in there to -1 the zeros should go. I had an int coming from a db join step with the same thing, the select values step sorted it nicely.

    stevens_ns

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

    Default

    It's just representation guys, an integer is still an integer.
    The thing that really changed is that the format during preview is the same as the one you expect after reading from a file and after writing to a file.

    There is absolutely no need to change the length or anything.

    Matt

  4. #4
    Join Date
    Jan 2006
    Posts
    12

    Default

    I have the same problem using older transformations which mapped integer values 0 and 1 to string 'N' and 'Y', using a Select values step to change the metadata and a Value mapper to do the actual mapping.

    The first problem was that, using Select values step to change the field's metadata from Integer to String(1), the string I got was ' 0001' (from a sql server smallint) - mind the leading space!

    Even using another Select values step to force the integer value, without the leading 0s, the leading space remains.

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

    Default

    If you use a mapping step, you would note that we made that backward compatible (no leading zeros nor leading space).

    If you convert the integer to boolean (boolean maps to Y/N) you would be safe too.

    So, how did you "Map" from 1 to Y?

    Matt

  6. #6
    Join Date
    May 2007
    Posts
    8

    Default

    Well maybe I'm wrong but I think it is rather confusing when the preview of a table shows leading zeros when the datatype is INT. Version 2.5.x displays the values as they were ment but thats my opnion.

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

    Default

    If you have plenty of time you can hunt down all the remarks we got because people were confused, right here on the forum.
    They got confused in version 2 because:

    1) the data in the output file looked nothing like the data previewed
    2) the data coming out of the text files read looked different in preview then the data in the text file.

    This behavior will be configurable in the future, but for now, that's what you get.

    All th ebest,
    Matt

  8. #8
    Join Date
    May 2007
    Posts
    8

    Default

    Ok

    Grtz. Harry

  9. #9
    Join Date
    Jan 2006
    Posts
    12

    Default

    I still don't think that things are as they are supposed to be right now.
    Look at this scenario: I have a smallint field in a source sql server table and I want to transform it in a string. Using a Select values step I change its type to string(1). The result? ' 0000', ' 0001'. Leading space, leading 0s.

    Ok, I might be confused about the expected result and the leading zeros and the string length are as they are supposed to be (even if the documentation doesn't mention this behavior). I don't give up and I put another Select values step in front of the first, forcing an explicit cast to integer. My field now follows the transformation smallint->integer->string(1). The result? ' 0', ' 1' etc. Still leading space, no more leading 0s. Much better, but not very consistent, IMHO.

    This wouldn't be much of a problem, if it weren't for the mappings that follow in my transformations. They worked in 2.5.2, but they aren't working now because of the leading space and zeroes.

    BTW, converting to boolean doesn't help, I get true/false values instead of Y/N.

    So, Matt, you suggest that I should convert my transformations to accommodate this behavior, because this is how it's going to be from now on?

    Thank you,
    Alin

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

    Default

    I wrote a whole version 3 data migration guide to explain one or two things.

    I'm actually finishing a patch for 3.0.1 that will allow you to set the explicit conversion mask in the "Select Values" step.
    The problem is not that the conversion happens one way or the other, the problem is that it was never defined how it was done.

    Matt

  11. #11
    Join Date
    May 2007
    Posts
    8

    Default

    Dear Matt,

    An example to give some input:

    The Generate D_DATE AU styleAU example in 3.0 GA is not working because in the "Calc Date" step the calculation of quarter is not working ok any more the result is 33 but it should return 1,2,3 or 4. Maybe this wil help you to solve something.

    grtz.

    Harry

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

    Default

    I doubt that very much. The specific issue you mentioned was fixed before we released 3.0GA.

    HTH,

    Matt

  13. #13
    Join Date
    May 2007
    Posts
    8

    Default

    Matt,

    I tried it again with 3.0 GA and still the result is Q33 and not Q1 and so on.

    Grtz,

    Harry

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

    Default

    Thanks Harry, I was actually looking at the "Generate D_DATE" sample, not the AU variation.
    How that AU variant works, is a mystery even to me ;-)
    I'm sure the cause of the problem is the . usage in the constants (regionally defined).

    I'll look into it later,

    Matt

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

    Default

    I fixed some more issues, some rather subtle and decided to add conversion masks to both the "Select Values" step metadata tabs as well as the "Calculator" step.
    In addition to that I set defaults on the Calculator to make it backward compatible with version 2.5.
    That way everyone should be happy.

    Try to update your PDI version 3 with these files by putting them in the lib/ directory:

    http://kettle3.s3.amazonaws.com/kettle-ui-swt-3.0.jar
    http://kettle3.s3.amazonaws.com/kettle-engine-3.0.jar

    Let me know if that helps you or not,

    Matt

  16. #16
    Join Date
    May 2007
    Posts
    8

    Smile

    Matt,

    I replaced the files, and if I'm right I have to upgrade the repository? anyway it worked for D_DATE AU styleAU so I don't know the subtle changes and there for I can't test them but for this it is ok. When are you releasing an officel 3.0.1?

    I think you are doing a very good job. keep up the good work!
    Did you heared anyone else bothering about the leading zero's?

    Grtz..

    Harry

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

    Default

    Hi Harry,

    I think we should be able to do a release on Friday next week.
    Getting rid of the leading zeros are a matter of setting the right metadata now.
    You can change it in the "Select Values" step.

    We could set a default "#" mask on the integers we generate and it wouldn't really make a difference.
    However, what *is* important in this stage is that we get rid of the "implicit" or "undefined" data conversion situations like in "Select Values", "Calculator", etc.

    You should upgrade the repository if you had issues, yes.

    Allerbeste groeten,

    Matt

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.