Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Output ??
This is duplicate thread, Don't like !! And please provide excel which you have. So then we can work for you
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.
Is that is the same?? Re: Autonumber Based on Suffix
Try
Max(0+Material)
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)
Hi! Anil,
Yes, I just broke down my discussion into 2 parts (max and autonumber).
Tks.
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;
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.