journal workflow

Question

I have a column called "spreadsheet key" and another one called "company id", so every time the company id changes, I want the spreadsheet key to be unique for each company id. For example, if the company id is 10000 then the key should be 1 but if it changes to 10001 then the key should be 2 and so on. How do I achieve this in Alteryx?

Bobbyt23 · Answer

Here is an example of both options @meghant03

Help Workflow 3.yxmd

KGT · Answer

In the solution by @Bobbyt23, you don't need the multi-row, a recordID will work there. I like that solution because it also gives you your lookup table for Cmpany-Spreadsheet.

If you want to use a multi-row though, the solution can be easier.

Sort by CompanyID

Multi-row: IF [CompanyID]==[Row-1:CompanyID] THEN [Row-1:SpreadsheetID] ELSE  [Row-1:SpreadsheetID]+1 ENDIF

I'll add that you can cut out the IF condition and just have [Row-1:SpreadsheetID] with CompanyID selected as a grouping field. But that can be hard to troubleshoot later if the tick in that box is not clear (i.e. lots of fields with CompanyID not being in the first few).

meghant03 · Answer

Can you explain the multi-formula step in more detail I am kind of confused? I don't want there to be 2 "spreadsheet key" columns in the final output.