Append in missing locations

[AntlRo]

Hello Everyone,

I have a spreadsheet with 7 Columns and I'm trying to append in Location to an existing field called location from a SQL DB. Below is an example of the spreadsheet I have( just a few columns). I only want to add/apped in the "location" where its blank to the existing spreadsheet from SQL. 

Example of spreadsheet:

NAme	Manager	Location	Date	Payment Code	Employee ID
John	Chris	Chicago	01/01/2022	r	123
Susie	Chris		02/01/2022	d	456
Matt	Jessica		03/01/2022	f	789
Logan	Jessica	California	04/01/2022	g	101

SQL DB Snapshot to pull location from(I already have the input with the correct connect string). ( keep in mind the SQL db has over a million records not 2, this just an example)

Table name: Employee Records

Employee ID	Location
456	Nebraska
789	Texas

End Result:

 I only want to add/append in the "location" column where its blank to the existing spreadsheet from SQL. Whichever the location is as the SQL DB has millions of records.. We are appending by the SQL Columns only to get the right location name ( example Nebraska, texas)

please post solultion in yxmd, best solution I will vote on. Thank you. 

[Felipe_Ribeir0 · Accepted solution]

Hi @AntlRo 

Your final output is an excel file, so i personally dont see how you can do something much more elaborate than this.

1)Import the current excel file to the workflow

2)Save the current excel file snapshot as a backup

3)Import the employee table from the DB

4)Left join both tables by the Employee ID columns and overwrite your initial file

If the item 3 is a problem because of the size of the DB table, you could use the dynamic input tool to import just the Employee ID`s that are on your Current excel file snapshot, adapting the idea from these topics: 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Transform-vertical-data-into-SQL-quot-...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/SQL-IN-Statement-1000-limit-Oracle/m-p...

[AntlRo]

Great This worked thanks, If your familiar with RegEX tool, I posted I think a fairly simple question I'm stuck on :

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Change-Last-name-order/td-p/1039378