Max Function with leading 0

[Anonymous]

Hello!  How can I get the max number andt keep the leading 0  based on last suffix... I tried using formula below but doesn't work.

max(Num(SubField(Material, '-',-1),Repeat('0',Len(SubField(Material, '-',-1)))))  or

num(max(SubField(Material, '-',-1),Repeat('0',Len(SubField(Material, '-',-1))))) 

Thank you in advance.

[Anil_Babu_Samineni]

Output ??

[Anil_Babu_Samineni]

This is duplicate thread, Don't like !! And please provide excel which you have. So then we can work for you

[Anonymous]

Hi!  Anil,

Pls. see attached.  Actually my objective is to create new material that has new Mfg Part Number using autonumber .    I actually have 2 options :

option 1 :

1.  Use  autonumber  to start after from the max suffix,,,,, pls. see 18 char count  tab in the attached.  The problem is if the material doesn't exist (Row 10 and 11) , I need to create  a new material with number format '0000' suffix.  However,   New Material field is limited to only 18 char.  If there is a way to auto reduce the leading 0 if the item I exceeds to 18 char ( New Material after reduce to 18 char)...., this would be better

Option 2 :

Since I was having difficulties in option 1, I tried to break down into a few fields..

1.  First determine the max suffix

2.  Counter - create a counter for unique materials (excluding last number suffix)

3.  New suffix Max suffix + counter -   this will be the autonumber

4. concat the subfield(OldMaterial,'-',-1)&new suffix - this will be my new material

I hope I have explained well.  Sorry , But I'm not really a techie person and still consider myself as novice.  I would really appreciate your help.

Thank you.

[Anil_Babu_Samineni]

Is that is the same?? Re: Autonumber Based on Suffix

[sasiparupudi1]

Try

Max(0+Material)

[cweiping]

Dear Caroline Acosta

maybe you can refrence this solution.

Load Script:

MData:

LOAD Material,

mid(Material,1,FindOneOf(Material, '-',SubStringCount(Material,'-'))-1) as Lead_Key,

TEXT('' & mid(Material,FindOneOf(Material, '-',SubStringCount(Material,'-'))+1)) as Num_Key,

     [Max (Desired output)]

FROM

[.\count.xls]

(biff, embedded labels, table is Max$);

Pivot Exp

=MaxString(total <Lead_Key> Num_Key)

[Anonymous]

Hi!  Anil,

Yes, I just broke down my discussion into 2 parts (max and autonumber).

Tks.

[maxgro · Accepted solution]

you can get the max with this script

Materials:

LOAD

    Material,

    Left(Material, Index(Material, '-', -1) -1) as MatStart,

    Text(SubField(Material, '-', -1))            as MatEnd,

    Len(Text(SubField(Material, '-', -1)))    as LenMatEnd

Inline [

Material

A1-10847-15-SM-0009

A1-10847-15-SM-0008

A1-10847-15-SM-0007

A1-10847-15-SM-0003

A1-10847-15-SM-0002

A1-10847-15-SM-0001

B1-3848-16-VC-03

B1-3848-16-VC-01

B1-3848-16-VC-05

B1-1010365-16-MC-1

B1-1010365-16-MC-2

B1-1010365-16-MC-3

B1-1010365-16-MC-4

];

Left Join (Materials)

load

    MatStart,

    MaxString(MatEnd) as MaxMatEnd

Resident Materials

group by MatStart;

[Anonymous]

Hi!  Weiping,

Thank you but is there any way to put the max in the script? ...coz I still need this in my other calculations.

Tks.