Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data manipulation - Changing Number Row

EvansM
9 - Comet

Hello Everybody,

 

I need some help to manipulate data in a column. Please refer attached two excel worksheets. One is ITEMNO-INPUT (Data sample)  and the second one is ITEMNO-OUTPUT (Expected Results)

 

I did try to use Multi- Row Formula, but the logic is too complicated to work the way I wanted.

 

I want the [CANOTEITEM] column to reflect ITEMNO - OUTPUT excel (Expected Results). This column needs to be INT Value.

 

Thanks In advance 

 

10 REPLIES 10
echuong1
Alteryx Alumni (Retired)

What logic is used to get to the values desired? Are you just trying to remove the leading zeros?

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @EvansM,

 

You could achieve this by simply changing the data type of the column to an INT using a select tool, INT64 would work perfectly fine.

 

image.png

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

afv2688
16 - Nebula
16 - Nebula

Hello @EvansM,

 

I used the multi row formula with the following:

 

IF IsEmpty([Row-1:CANOTEITEM]) THEN 101
ELSEIF Right([Row-1:CANOTEITEM],1)='1' THEN [Row-1:New Field]+1 ELSE [Row-1:New Field]+99 ENDIF

 

Untitled.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

 

EvansM
9 - Comet

@echuong1  I am not trying to remove the Zeros. I want to [CANOTITEM] on INPUT to look like [CANOTITEM] on OUTPUT.

 

If you look at [CANOTITEM] on OUTPUT. Is very different with [CANOTITEM] on INPUT.

That is how I want to look.

 

 

 

afv2688
16 - Nebula
16 - Nebula

Hello @EvansM ,

 

Edited my previous post with the solution.

 

Regards

EvansM
9 - Comet

@afv2688 Thank you very much for your quick response. This is very close to the expected results. The problem is line 43 of OUTPUT [CANOTEITEM] needs to go back to 101 because the [CUSt_NO] changes.

 

This means very time  [CUSt_NO] change,[CANOTEITEM] needs to go back to 101.

That is the logic

 

afv2688
16 - Nebula
16 - Nebula

Hello @EvansM ,

 

Done 🙂

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

echuong1
Alteryx Alumni (Retired)

See attached - you just need to add the condition in the multi-row formula to group by customer number.

 

echuong1_0-1579280932153.png

EvansM
9 - Comet

@Jonathan-Sherman  Thank you very much for the response. Please refer to those two excel worksheets.I want [CANOITEIM] for ITEMNO - INPUT to look like [CANOITEIM] for ITEMNO - OUTPUT.

 

ITEMNO - INPUT is the data sample.

ITEMNO - OUTPUT is the expected results

 

Labels