Append in missing locations

Question

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 ManagerLocationDatePayment CodeEmployee ID    JohnChrisChicago01/01/2022r123    SusieChris 02/01/2022d456    MattJessica 03/01/2022f789    LoganJessicaCalifornia04/01/2022g101

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 IDLocation        456Nebraska        789Texas

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 Answer

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-In-quot-statement/m-p/1027002#M253903

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