Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Tansformation to set values of a field depending on value of another Input Field

  1. #1

    Default Tansformation to set values of a field depending on value of another Input Field

    Dear All,

    Can someone help me for the following scenario:

    Input:
    Category Tax ID Supplier Name of Shop
    1 wholesale
    2 Individual
    3 Group

    In my input fields TAX ID is dependent on the CATEGORY and NAME OF SHOP is dependent on the SUPPLIER
    I am trying to find right transformation to set the values of TAX ID & NAME OF SHOP with the following mapping based on the other field values as shown in the output.
    1 = 11; 2 = 12; 3 =13. ( eg: when category = 1 then SHOP ID=11)
    Wholesale = a; Individual = b; Group = C (eg: when ‘supplier = wholesale’ then ‘ name of shop =1’)
    Output:
    Category Tax ID Supplier Name of Shop
    1 11 wholesale a
    2 12 Individual b
    3 13 Group c

    I was trying to use USER DEFINED JAVA CLASS but it seems to be very complicated in Kettle. Can someone suggest me which transformations I can use?

    Regards,
    Rama

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Have you looked at Value Mapper?
    -- Mick --

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

    Default

    I see a classical use-case fro Stream-Lookup here.
    So long, and thanks for all the fish.

  4. #4

    Default

    @Mick: Value Mapper does not help me here!

    @
    marabu: I tried to use Stream-Lookup but I still did not find the option to mention the following:

    when category = 1 then SHOP ID=11
    when ‘supplier = wholesale’ then ‘ name of shop =1

    Kindly see the screenshot attched.

    if I am right, this
    Stream-Lookup gives only the option of mapping fields from different inputs. can you kindly provide more information.

    regards,
    Rama
    Name:  screenshot.jpg
Views: 1650
Size:  26.5 KB



  5. #5
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    You can solve this in PDI using different combination of steps, so what I suggest is not the best solution but just one of them.
    when category = 1 then SHOP ID=11
    I would use Value Mapper for this. You can also use Modified Java Script, or Filter Rows and Select value (to drop a field) with Add Constant (to recreate the same field with updated value).

    when ‘supplier = wholesale’ then ‘ name of shop =1
    As above, filter rows, select value to drop a field, then calculator or add constant.

    If you use the filter rows, you need to combine your two streams using Append Stream step.
    -- Mick --

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

    Default

    Quote Originally Posted by theperk2 View Post

    if I am right, this
    Stream-Lookup gives only the option of mapping fields from different inputs. can you kindly provide more information.
    That's true, so if you create a stream that has:
    Category | TaxID
    1 | 11
    2 | 22
    3 | 33

    Then based on your stream values of Category, you can look up the TaxID. This method allows you to keep a CSV file of all of the categories and TaxIDs, and not need to recode your logic if one of them changes.

    Mick's idea of using the Value Mapper step will work, but has the down side of needing to be recoded if a TaxID changes.

    As you can see, there are many ways of doing things in PDI, and what is best for your situation will be dependent on what you can explain to the other developers in your organization, and what works best in your testing.

    This is *NOT* a "give me the code" type site.

  7. #7

    Default

    Hi Marabu,

    that is exactly my problem, where exactly I can create the following stream: (from my screenshot erlier, I can only get the input values and and rename them)

    That's true, so if you create a stream that has:

    Category | TaxID
    1 | 11
    2 | 22
    3 | 33

    can kindly explain from my screenshot view?

  8. #8
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    I think that you have to create a text file (or DB table) with those values.
    Then use a Text File Input or Table Input and Stream Lookup step.
    -- Mick --

  9. #9

    Default

    hi Mick,

    finally it is working. thanks once again for the help.

    regards,
    Rama

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.