Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cacosta35
Creator II
Creator II

Max Function with leading 0

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.

12 Replies
cacosta35
Creator II
Creator II
Author

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

vvira1316
Specialist II
Specialist II

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:

Capture.PNG