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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

1.png

View solution in original post

12 Replies
Anil_Babu_Samineni

Output ??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
cacosta35
Creator II
Creator II
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sasiparupudi1
Master III
Master III

Try

Max(0+Material)

cweiping
Contributor III
Contributor III

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)

Ans_284419.png

cacosta35
Creator II
Creator II
Author

Hi!  Anil,

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

Tks.

maxgro
MVP
MVP

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;

1.png

View solution in original post

cacosta35
Creator II
Creator II
Author

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.