topic Re: Max Function with leading 0 in QlikView

Max Function with leading 0

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Max Function with leading 0

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:06:12 GMT

Output ??

Re: Max Function with leading 0

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:24:15 GMT

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

Re: Max Function with leading 0

Anonymous — Fri, 08 Dec 2017 02:07:44 GMT

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.

Re: Max Function with leading 0

Anil_Babu_Samineni — Fri, 08 Dec 2017 02:24:23 GMT

Is that is the same?? Re: Autonumber Based on Suffix

Re: Max Function with leading 0

sasiparupudi1 — Fri, 08 Dec 2017 14:54:25 GMT

Try

Max(0+Material)

Re: Max Function with leading 0

cweiping — Fri, 08 Dec 2017 16:08:08 GMT

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)

Re: Max Function with leading 0

Anonymous — Fri, 08 Dec 2017 18:32:22 GMT

Hi! Anil,

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

Tks.

Re: Max Function with leading 0

maxgro — Fri, 08 Dec 2017 20:10:47 GMT

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;

Re: Max Function with leading 0

Anonymous — Fri, 08 Dec 2017 21:37:47 GMT

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.

Re: Max Function with leading 0

Anonymous — Fri, 08 Dec 2017 23:44:45 GMT

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

Re: Max Function with leading 0

vvira1316 — Fri, 08 Dec 2017 23:53:52 GMT

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

Re: Max Function with leading 0

tamilarasu — Sat, 09 Dec 2017 11:03:50 GMT

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: