Combining Data with Increments

[KamenRider]

Good day,

I would like to ask for your assistance how I can include increments as results of my data. The grouping of my result is composed of 5 category concatenating the following:

1. Box Number

2. Code

3. For each name, there is a corresponding code which increment, for example all data with BPO should increment regardless if it is not chronological. See screenshot below

4. Month and Year 123 means January 2023 as shown below.. therefore if it is February, the result should be 223.

By the way, under box number column, 133106720-APP should be 133106720 only. Same with 453106720-BPO which should be 453106720 only. 

The combined category shown below is the result I would like to do, my problem is what formula or tool should I used to have it incremented.

 

Hope to help me in this problem.

Thanks,

Kamen

[ShankerV]

@KamenRider 

For the last point, this is mentioned on the current date the workflow is run?

4. Month and Year 123 means January 2023 as shown below.. therefore if it is February, the result should be 223.

It the same workflow run on Feb it should be 223?

Many thanks

Shanker V

[ShankerV]

Hi @KamenRider 

++ One more help, can you copy and paste the data instead of a screenshot.

Thanks in advance.

[JamesCharnley]

Hi @KamenRider 

One way to address the point 3 is to reorder your rows with a sort tool so a multi-row formula can be applied to add the incremental tag. To retain chronological order afterwards, you can add a RecordID before the sort and then sort again on Record ID afterwards. Once that's made you can just add all the pieces together in a formula.

There's no date field included in your picture, but if you have a date field you'd just have to use the DateTimeFormat function to get it in the correct format (example included in my workbook if you download).

[ShankerV]

Hi @KamenRider 

One way of doing this.

Expected output:

Input:

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

Many thanks

Shanker V

[ShankerV]

Hi @KamenRider 

Step 1: Input tool

Step 2: Regex 

This step is performed to satisfy this condition

By the way, under box number column, 133106720-APP should be 133106720 only. Same with 453106720-BPO which should be 453106720 only. 

Step 3: 

Many thanks

Shanker V

[ShankerV]

Hi @KamenRider 

Step 4: 

Step 5:

Step 6:

[Box Number]+"-"+[Code]+"-"+tostring([Tile_SequenceNum])+"-123"

Expected output:

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

Many thanks

Shanker V

[KamenRider]

Hi @JamesCharnley 

I like the solution except for the date column. I don't have the date column. Is it possible to determine the jan 2023 as 123 even though without the date column? Also some of my data has _R and if the data has _R, it should be included in the result.

Please advise,

Thanks,

Kamen

[JamesCharnley]

Hi @KamenRider 

Just ignore the date part then that was just a guess! If you don't have row level date to calculate then the only other option that springs to mind is using datetimenow to pull date from when the workflow is run. If the rows need a different date I can't think of a way that Alteryx can know that if it's not on a row level. How do you know which month it needs without a column? If it's something like it's run that month/the month after or x number of rows for each month for example we could find a way to do it.

The _R will work if it's in the box number column as is, I just didn't put it in my text input when typing it out. It even eliminates the need for the first select because it'll automatically come in as a string.

[KamenRider]

Hi @ShankerV 

The solution is good except that I noticed that the code is being sorted to get the increments. I would like data to be at ease the way it is presented plus it should continue its increment. For example,

1234_R-APP-1-123

5478-BPO-1-123

2345-APP-2-123

1245--LIT-1-123

4578-APP-3-123

4578-BPO-2-123

Please advised,

Kamen