10 Replies Latest reply on Aug 3, 2016 2:28 AM by Ankit Goyal

    Lookup values based on conditions

    Dibyendu Sharma Mondal

      Dear All,

       

      I am trying to do a blending to arrive at a High Cost Lost cost country definition in one table and then I have data like headcount, ratings, salary etc in another table. Below is the scenario

       

      1. Table HighCost_LowCost - Fields are:

      • Country (like India, Australia, Unites States)
      • Country Code (3 letter code like IND, AUS, USA)
      • High Cost/Low Cost Flag (like: High, Low)

       

       

      2. Table Details - Fields are

      • Emp ID
      • Name
      • Ratings
      • Salary
      • Country
      • Emp Category
      • etc....

       

       

      I want to find out the High Cost and Low Cost flag from the Table 1 to Table 2 and show the trend of headcount over time. However while doing blending I am able to pull up the detail. The challenge comes when the data in Table 2 for country is a mix of both Country and Country Code, hence all with Country code is returning "NULL" when pulled in view to see High Cost & Low Cost Headcount numbers

       

      What I thought is to write a logic and create a normalized field

       

      IF LEN ([TABLE2.COUNTRY]) = 3 THEN find the value from the [TABLE 1.COUNTRY CODE] and return [TABLE 1.COUNTRY] ELSE [TABLE2.COUNTRY]
      

       

      and then pull the High Cost/Low Cost Flag in the view (Not sure if blending would still work)

       

      The challenge I am facing is I am not sure how do I achieve this because for Blending IF condition doesn't work without Aggregation in Table Calc as well as from different tables.

       

      Also I am not sure if I could achieve the below logic, if yes can you tell me how to I do I return the value (in Excel would have been easy just to use a VLOOKUP)

       

      Request your suggestion or some alternate method please

       

      Regards,

      Dibyendu

       

      Message was edited by: Dibyendu Sharma Mondal