Updating Second file based on First file newly generated column

[areeba]

Hi I have two input files, i need to merge them  so i used join tool and made it full outer join twice  to merge my 2 different files. It worked well.

I have formula which is creating a new column 'New Column' by taking 2 files:

If Level1(File 1) = Work(File 2) then Function(Value Function)

Elseif Level2(File 1) = work (File 2) then Function (Value of Function)

Else '#N/a'

Endif.

For eg:

File 1

Employee_ID      Level1         Level2 

1001                    Support      

1002                    Director       Line

1003                    Trader          Manager

File 2:

Work              Function

Support          Direction

Line                 Services

Power             Change

Which is giving this Output:

Employee ID             Level1        Level2                      New Column

1001                          Support      blank(no null)         Direction

1002                          Director      Line                          Services

1003                          Trader         Power                      #N/A

Now, My requirement is : Is it possible to put a check  that if that 'New Column ' gives 'N/A' value then  user interface /prompt appears asks user to fill column Work ,Function (of file 2) and update file 2  as a new output file  with a new line ;

Expected Output:

File 2

Work              Function

Support          Direction

Line                Services

Power            Change

Utility             operation (new line)

Regards

Areeba

[OllieClarke]

Hi @areeba 

I'm not sure I fully understand your question, as I don't see where the "Utility      operation" value comes from. However, you can use a message tool or a test tool to inform your user that there is a "#N/a" in a column. If you use the test tool, then this would result in a workflow error, if you use a message tool then you have more control as to how this message appears. The following workflow uses both a test and message tool to error and inform the user that there was a "#N/a" in the [New Column].

Hope that helps,

Ollie

[areeba]

Hi @OllieClarke 

Thank you for replying, but the requirement is not to inform user that column has N/A rather the requirement is if Newly generated column ‘New column’ (from two different files) has N/A value then I need to update my second file with a new record ( here I gave example: Utility , Power)as a new output file. The main requirement is if new column has N/A then a parameter/prompt appears which allows user to enter value during runtime and update work and Function ( columns of file 2) and generate a new output file with new records . 

To be more specific: my client manually updates this second file if ‘New column’ has N/A . I just need to automate it in a way that they can pass the value on runtime of workflow and their second file gets updated as a new output file. 

I hope I am clear , please let me know if you need more clarification. 

Is it possible in Alteryx,can you suggest a solution? 

Regards 

Areeba

[aesanchez]

Hi Areeba,

Building onto @OllieClarke's file, please see the attachment.

This leverages an external file where users can input their custom entries. It would certainly require to run the file at least twice. The first run would identify any errors, which would be flagged by the tools proposed by OllieClarke. The user could then load the requried custom values on to a local file (.xlsx?) which would be leveraged during the second run.

Unfortunately, I do not know of options to enable user input mid-stream. I'm guessing someone has already figured out a more elegant way of handling this.

[AkimasaKajitani]

Hi @areeba 

As far as I know, Alteryx doesn't allow users to stop the workflow of the process and enter the data on the way.
The first run will notify the problem for the user, update the data, and then run it again.

There is a way for the user to update File2 at this time, or there is a way for the user to prepare a file to add File2, put it in a specific folder, and then merge it with File2 in the workflow.
What I often do is output a list so that users know what needs to be added at this time.

However, if the workflow takes a long time to execute, users want to enter it on the way.
You might want to consider stopping the workflow on the way if the result have #N/A.

I make sample workflow.

If New Column has Null(#N/A)
- stop the data
- output list for what is needed