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.

[Anonymous]

Thank you !  Massimo....it's exactly what I wanted...  Thank you once again.

[vvira1316]

If you have got answer then please close the other thread.Adding text numbers with Leading 0

[tamilarasu]

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: