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

# Lookup values based on conditions

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)

Regards,

Dibyendu

Message was edited by: Dibyendu Sharma Mondal

• ###### 1. Re: Lookup values based on conditions

A sample data or workbook in .twbx format will be more helpful for us to anyalyze your issue. Kindly share

• ###### 2. Re: Lookup values based on conditions

Mahfooj Khan just attached in the original post, please have a look

• ###### 3. Re: Lookup values based on conditions

Is that what you after?

Workbook (version 9.3) has been attached for your reference. Let me know If you've any query.

Mahfooj

• ###### 4. Re: Lookup values based on conditions

Hi Mahfooj,

appreciate you taking your time to help. But I am looking for something that I have mentioned above.

View would have say Headcount number over time. Then a flag separating High & Low for each region which will come from the Details table.

Since the details table for Country column we have both values in same column i.e. Country as well as Country Code. While doing the above view it would return NULL for all those where country code is there since the blending is looking for country and not country code typically.

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

1. 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)

• ###### 5. Re: Lookup values based on conditions

Can you explain what you do mean to say by headcounts here? I'm not getting such field in your data sources. Kindly explain and share your expected output.

• ###### 6. Re: Lookup values based on conditions

Basically the count of Emp ID is headcount

• ###### 7. Re: Lookup values based on conditions

Are your looking after something like this:

Option1

Each line represent high/low cost with heads counts over reporting timeframe. I've also given country code selector.

Option2:

Country wise head counts over time with high/low cost

Let me know If you've any query.

• ###### 9. Re: Lookup values based on conditions

Have a look.

You've to join your both the data sources. Then only you can get this result. Blending will not work in this case.

Sharing you the workbook (version 9.3). Let me know If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 10. Re: Lookup values based on conditions

See if this helps !