Remove last n rows

akmuthu

1. How to remove last n rows with a single tool? Sample tool will retrieve last n rows, but doesn't remove last n rows right? 

2. Also, in terms of workflow constants, why and when do we use these engine constants? Can we multiple workflow constants in a single workflow? Thanks.

Taylor_S

Hello! 

To answer your first question, there are a number of ways you can skip the last N Rows with a single tool, however it somewhat depends on your workflow and the input. Let's say you have 5 records, and you want to skip the last 2 which are footer related. One way would be to just pull the first 3 using the First N Rows option of a Sample tool and setting N = 3. However, if the input is changing, meaning that the number of records can shift, this will become a problem and isn't the most dynamic solution as you may end up missing some data. Therefore, your best option would be to either to use multiple tools (such as a Sort Tool set to Descending order, followed by a Sample set to Skip 1st N Rows, and then followed once more by a Sort Tool set to Ascending order to revert the initial flip) or to download the Crew macro associated with skipping the last N rows. It all comes down to what you are trying to remove and if the dataflow input is going to be changing.

To answer your second question, engine workflow constants can be used a number of ways and honestly to list them all would make this post longer than it already is. But I can speak to my own most recent use at the very least. As a constant you will likely use these within Expression boxes found within tools like the Formula or Filter. Recently, I built a macro that, among other functions, sends an email to colleagues informing them that a particular workflow has been updated. For the email component, I built both the Body and Subject within a Formula tool as individual fields to be fed into the Email Tool. Using the WorkflowFileName constant within an expression ([Engine.WorkflowFileName] + " has been updated."), I was able to ensure that upon running, the macro pulls the outer workflow's file name (let's call it Workfow A) and places it within a corresponding message of both the subject and body fields (i.e. "Workflow A has been updated") so that the reader knows exactly what workflow changed. Now, regarding whether multiple can be used, I think that largely depends on how you are using them. For example, I could use the engine constant directed at workflow file name in multiple instances for something like a message in an email. But for something where perhaps you are manipulating Temp File paths or the workflow directory, you might want to be careful.

The following link gives a little more about the engine constants if you have interest: Constants | Alteryx Help

Again, this is just one way that an engine constant can be used. As I mentioned there are far more cases out there that could be discussed (many of which lie in realms of file inputs, outputs, pathing, etc) and I'm sure far more to come.  But hopefully this gives you some insight as to how they could be used.