Problem with User prompt value not getting updated as a column value

Question

Hi,

I want to create an Alteryx App, wherein I have two field Month and ID, I want to prompt user to enter Month value and it should get inserted with a new id (id+1).

Eg.

Month ID

Jan       1

Feb       2

So, if user enter 'Mar', a new record with ID as '3' gets inserted into the table. I tried to create it, please refer to the attached workflow.

Mar  3

Test.yxwz

BenMoss · Accepted Answer

Here is my attempt at achieving what you want.

I used the text input in a similar manor to the post above, and then join against an aggregated data stream (group by month max ID).

If the record joins, that means the record does exists, I can then take the max value and add 1.

If the record doesn't join, that means the month does not exist in the DB already and the Id 1 is assigned.

Example attached.

Ben

BenMoss · Accepted Answer

I've looked at @CharlieS's solution now and I've approached it in a different manor, because I've looked to increment the ID based on that months data only, where as charlie does it across the entire dataset, which makes sense and is probably what you were looking for!

Ben

tcroberts · Accepted Answer

I've taken a slightly different approach and just updated an 'empty' Text Input tool with the value from the App question, then unioned it into the main data stream (after sorting by ID ascending), and added a MultiRow Formula Tool with the expression:

[Row-1:ID]+1

used to update the existing field [ID]

I may be missing something, but this should accomplish what you want in a relatively simple way, since after ordering by ID each consecutive row should already be the previous row + 1, and so only the new entry will be updated. You could also add another sorting tool afterwards if you need the output to be in a different format.

Cheers!