10 Replies Latest reply on Jun 22, 2018 12:36 PM by Gerardo Varela

Creating Calculated field from a vlookup statement in Excel

Does anyone know how to create a calculated field in TAbleau based on the following vlookup statement from excel?

=IFERROR(VLOOKUP(A2,Holidays!A:B,2,FALSE),IF(WEEKDAY(A2)=1,"Sunday",IF(WEEKDAY(A2)=7,"Saturday","No")))

• 1. Re: Creating Calculated field from a vlookup statement in Excel

Hi Michell

what are you trying to do - it looks like you are trying to assign a "Holiday" Name ( ore weekend) to a date

if that is not the question the please explain

Tableau and excel don'e have the same architecture - its more like access - and it would be easier to accomplish your goal here by joining 2 tables and doing a conditional statement

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Creating Calculated field from a vlookup statement in Excel

Hi Jim,

Thanks for replying. I am trying to create a calculated field that indicates if it is a holiday or weekend. The excel file is attached if that is helpful.

I am not familiar with vlookup statements, so its hard to figure out how to create the calculated field.

Any help would be great!

Michelle

• 3. Re: Creating Calculated field from a vlookup statement in Excel

Michelle,

but may be a useful general reference on weekends and holidays:

• 4. Re: Creating Calculated field from a vlookup statement in Excel

Hi All,

So the vlookup is looking to check whether or not the transaction date is a holiday, if it isn't check to see if it's Saturday/Sunday.  I've replicated the look up in Tableau.  I left joined your Data sheet with Holidays on Transaction Date and Gildead Holiday.  You have to make sure both data types have been converted to dates before you make the join:

Then it's a just a calculated field to mimic the lookup:

IF

THEN "Holiday"

ELSEIF

DATEPART('weekday',[Transaction Date])= 7

THEN

"Saturday"

ELSEIF

DATEPART('weekday',[Transaction Date])= 1

THEN

"Sunday"

ELSE

"False"

END

Attached workbook version 10.5.  Let me know if you have any questions!

Regards,

Gerardo

• 5. Re: Creating Calculated field from a vlookup statement in Excel

Hi

Thanks for sending that over -

In a database oriented system like Tableau (or Access) Vlookup - or hlookup  like you are doing here can be accomplished by joining 2 files together

I took your Holiday list and joined it to the Superstore data filter it for year and produced the attached

the process is shown below - on the data source tab - the formula date(Gilead Holiday) is there to convert what came in as a string to a date

what this day was place "Holiday" in a dimension called Holiday Indicaor)

then on the viz I added this calculation

what it does if the holiday indicator is NOT null it will take the value (Holiday") if it is null it will determine the day of the week and place it in a dimension that will be used as a filter

then create the viz - the first is detail

Just a list of dates and the sales value on that date (yours would be expenses)

Note I filter it so you are just seeing holiday sat and sun

the summary without dates would look like this

or the weekday summary would look like this

Note also If HR issues the new list of Holidays for next year just add them to the Holiday list and refresh the file in Tableau

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 6. Re: Creating Calculated field from a vlookup statement in Excel

This was SO helpful! And worked great.

I have another questions. Would you know how to write a tableau calculated field for this?

When cct.mis_industry_code >=3000 and cct.mis_industry_code <=3299 then 'Yes - Airline'

When cct.mis_industry_code >=3501 and cct.mis_industry_code <=3799 then 'Yes - Hotels'

When cct.mis_industry_code = 5411 then 'Yes - Grocery Store'

When cct.mis_industry_code = 5499 then 'Yes - Misc Food Store'

When cct.mis_industry_code = 5812 then 'Yes - Eating Places/Restaurants'

When cct.mis_industry_code = 5814 then 'Yes - Fast Food'

When cct.mis_industry_code = 7832 then 'Yes - Motion Picture Theaters'

• 7. Re: Creating Calculated field from a vlookup statement in Excel

This is really a separate question

Please mark my reply correct to close the first thread and post his as a new one

Also What is it you are trying to do - if there is a Text name for each Code you can join another table like above

Jim

• 8. Re: Creating Calculated field from a vlookup statement in Excel

Hi Michelle,

Like Jim stated, this should be candidate for it's own question. Anyhow, I can't verify it, but something like this:

IF

cct.mis_industry_code >=3000

and cct.mis_industry_code <=3299

then

'Yes - Airline'

ELSEIF

cct.mis_industry_code >=3501

and cct.mis_industry_code <=3799

then

'Yes - Hotels'

ELSEIF

cct.mis_industry_code = 5411

then

'Yes - Grocery Store'

ELSEIF

cct.mis_industry_code = 5499

then

'Yes - Misc Food Store'

ELSEIF

cct.mis_industry_code = 5812

then

'Yes - Eating Places/Restaurants'

ELSEIF

cct.mis_industry_code = 5814

then

'Yes - Fast Food'

ELSEIF

cct.mis_industry_code = 7832

then

'Yes - Motion Picture Theaters'

END

It seems you're fairly new to Tableau so here are some helpful links:

Regards,

Gerardo

• 9. Re: Creating Calculated field from a vlookup statement in Excel

Thank you Gerardo!

• 10. Re: Creating Calculated field from a vlookup statement in Excel

You're welcome!

Regards,

Gerardo