How to rename data (prior to data output to excel) like I could with a vlookup in excel.

[Henry_Gunn]

Hello,

I am trying to automate a daily report which would save me a great deal of time everyday. I have the basic operations of my workflow down, but am trying to set in some rules that would take effect before the data is output to an excel sheet.

The 3 big columns I am hoping to change are:

1. "Quantity", which is measured in 1000s, so I am hoping to divide each entry in the column by 1,000 before the data is exported.
2. "Effective Date", which I am hoping to switch the format of from "yyyy-mm-dd" to "mmmyy" (like Aug22 for Aug 2022 with no day).
3. "Location", there are 4 basic locations which I am responsible for reporting on, but after each entry (in the same cell) is a complicated "after label", to show the actual container within the location that each specific shipment came from, which is irrelevant to my task. I'm hoping to do like I used to in excel with vlookups to have something like "every time the base info in a cell says 'A', return 'X"', and so forth, to present the data as cleanly as possible without the container info which no one wants. 

For example it might read "New York - A154768T1" and I would like it just to report back something like "NYC" with nothing else.

Please let me know what you think, I would really appreciate any help you can offer.

Henry

[ChrisTX]

For Quantity, can you use a Formula tool to create a new field, dividing the original value by 1000?

For Effective Date, what is the data type of the incoming data?   Is it a Date, Date Time, or String?  Take a look at the DateTime functions, to use in a Formula tool.

https://help.alteryx.com/20221/designer/datetime-functions

For Location, again try a Formula tool to create a new field with something like 

IF Contains([original field], "New York") then "NYC"

ELSEIF Contains([original field], "Los Angeles") then "LA"

ELSE "<unknown>"

ENDIF

or try the Find/Replace tool.

Then you can use a Select tool to drop your original fields, and re-name and re-position your new fields if you need to.

Chris

[DavidSkaife · Accepted solution]

Hi @Henry_Gunn 

Here is one way of doing it:

A formula tool that updates the 'Quantity' field by divides the data by 1000, one that creates a new date field formatted in the right way (as a string field), and a lookup that matches the start of the 'Location' field and replaces the text.