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.
Thank you ! Massimo....it's exactly what I wanted... Thank you once again.
If you have got answer then please close the other thread.Adding text numbers with Leading 0
Hi Caroline,
I believe you want max numbers after converting the material numbers to 18 digit. If so, the below solution converts material number to 18 digit and then shows max number from those material part numbers. Have a good weekend!!
Script:
Data:
LOAD Material,
Replace(Material,Subfield(Material,'-',-1),'') & Mid(Subfield(Material,'-',-1),If(Len(Material)-17 > 0,Len(Material)-17,1)) as [New Material 18 char], //18 digit conversion
Left(Material,Len(Material)-Len(Subfield(Material,'-',-1))) as [Material (Without Part)], //Materials without part number
Text(Mid(Subfield(Material,'-',-1),If(Len(Material)-17 > 0,Len(Material)-17,1))) as PartNo // Partno in text format
FROM
count.xls
(biff, embedded labels, table is [Max$]);
Left Join
MaxLoad:
Load Distinct [Material (Without Part)],
Maxstring(PartNo) as MaxSuffix, //Max part number in text format
Count([Material (Without Part)]) as Counter // Count of material without part numbers
Resident Data Group By [Material (Without Part)];
Result: