This post is subsequent of the previous post, which is about error detecting in Alteryx. This is based on the same use case of the previous post. This post is about how to use the Formula tool for error detection.
Use Case
The input data is as following transaction.
And use this product master file.
When you want to summarize the transaction data, join both two data with ProductID as key and multiply the Unit Price and Qty.
The check point which you have to check is L anchor of the Join tool because if some data is unregistered for the Product Master, it will be output at the L anchor.
Error detecting by the Formula tool with Message function
You can use the Message() function in the Formula tool. This function show the message at the Result window as the log, meanwhile it output values as data, which means that it has multi-function.
Message() function
Message() function is the one of the function in the Specialized category. The syntax is as follows.
Message(messageType, message, returnValue)The “messageType” is the integer of follows.
- 1 : Messages
- 2 : Warnings
- 3 : Errors
- 5 : Conv Errors (field conversion errors)
- 8 : Files(input)
- 9 : Files(output)
This is like the “Message Type” in the message tool.
The “message” is for the message that is you want to show in the Result Window. The “returnValue” is what you want to return as data.
For example, let’s think about the sample use case. Using the Formula tool with Message function, if the ProductID is not in the Product Master, you can set the value as 0 and show the error. In this case, you can make such a workflow as follows.
In this workflow, the data coming from L output anchor at the Join tool has null data which doesn’t match with the Product Master.
The Product ID of these records with null data are not registered in Product Master, so I’d like to use the Message function to show them as the error. The setting is as follows.
IF IsNull([Product Name]) THEN
Message(3, "ProductID is not exist : " +ToString([ProductID]), 0)
ELSE [Qty]*[Unit Price]
ENDIFThis will show the error in the Results Window as follows.
Futhermore, the message function output 0 insted of null in the Sales column.
Saving the error logs by Output Data tool with Message() function
Actually, many people want to save a file which has the reason why error occurs. To use the message function in the Formula tool, you can make the column which contains why error occurs.
In this case, you can write message function simply in the Formula tool.
Message(3, "Product Master Error : "+ToString([ProductID]), [ProductID])However, this workflow will make a file even if there are no errors. If you don’t want make the file when there are no errors, you need to control the Output Data tool not to make a file by the Batch macro. The workflow for this way is as follows.
The important point is that the Control Parameter tool is received the record number. If it is zero, the Action tool will disable the Output Data tool. Note that the recent Designer(2022.3 an d onwards) have the “Disable Tool” option in the Output Data tool, the older Designer doesn’t have it. If you are using older version, you can use the Tool Container instead of that option.
The Action tool setting is as follows. Please select “Disable – value” on the “Value or attribute to Update” option with “Update Value with Formula” option.
The “Formula” is as follows.
IF [#1]="0" THEN "True" ELSE "False" ENDIFHere, let’s think the actual operation. If there is older error file, you might be under the impression that there is an error now. On the other hand, if you use it at Alteryx Server, you can ignore it because the Server runs the workflow in a different folder every time.
For that, you can use the Run Command tool. You need to make a workflow to use a batch file to delete the files before the new file is generated.
Please see the sample workflow if you want to know the detail.
Conclusion
- I have explained how to detect the error by message function in the Formula tool.
- I think that the Message tool is easy to use, but there is few situations to require the message function in the Formula tool.
- But the good point is not required the Message tool. You only need the Formula tool.
Sample workflow download
The next blog post is…
The next blog is about error detection, too.
By the way, this blog series in the Japanese has been started since July 10th, 2023. English version is September 26th, 2023. I’m going to write 100 blogs at least, so I have completed half. This translate project has been to rely as little on translation tools as possible. I have to always change how to say in Japanese to in English, so I feel the difference, again.
I don’t know how long I can continue, but the next goal is 100 articles! I’m glad to enjoy my articles.
コメント