Subfield Issue

[Anonymous]

Hello!

I have an items that I'm trying to get the last suffix (numbers) that are not consistent....I used the formula

subfield(Material '-',-1)  but some results are not  my expectations :

For Ex :

Material                                            Results :                 

23-ABX-01-SY-004                          0004 -   should be 004

24-ABY-01-SY-04                            04

25 -ABS-02-SY-003                          003

26 -BBS-02-SY-0003                        003 should be 0003

35 -BBC-05-SY-0011                         11  should be 0011

what's wrong with the logic?

Also, after the results, I'd like to add them by 1 them but keep the format on Results.  However, the results are in numbers...

     Expected Output

Material                                            Result :                       Result+1              

23-ABX-01-SY-004                           004                             005

24-ABY-01-SY-04                            04                                 05

25 -ABS-02-SY-003                          003                               004

26 -BBS-02-SY-0003                         0003                           0004

35 -BBC-05-SY-0011                           0011                          0012

Pls. advise...

tks

[vishsaggi]

Use like

subfield(Material,  '-', 5)

[Anonymous]

Hi!

Actually, it's not just 5 ...2 some materials could be  24-ABY-SY-04 .  Anyway, I still tried your formula but still the same results.

Thank you for your time.

[vishsaggi]

IS SY common in all the values you get ?

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD Material,

Text(SubField(Material, '-', -1)) as Result,

Text(Num(SubField(Material, '-', -1)+1,  Repeat(0, Len(SubField(Material, '-', -1))))) as [Result+1];

LOAD * INLINE [

    Material

    23-ABX-01-SY-004

    24-ABY-01-SY-04

    25 -ABS-02-SY-003

    26 -BBS-02-SY-0003

    35 -BBC-05-SY-0011

];

[MarcoWedel]

Hi Sunny ,

I just was about to post this when I saw your answer:

table1:

LOAD *,

     SubField(Material, '-',-1) as Result1,

     Text(SubField(Material, '-',-1)) as Result,

     Text(Num(SubField(Material, '-',-1)+1,Repeat('0',Len(SubField(Material, '-',-1))))) as [Result+1]

INLINE [

    Material

    23-ABX-01-SY-004

    24-ABY-01-SY-04

    25 -ABS-02-SY-003

    26 -BBS-02-SY-0003

    35 -BBC-05-SY-0011

];

have a nice day

Marco

[MarcoWedel]

To add an explanation for the OP:

The same numerical value can only be represented with one single format per field, i.e. the format of the first occurrence of a numerical value defines the format of subsequent occurrences of the same numerical value in the same field.

Try changing the order of your sample data to see this behaviour.

So one solution would be to use text values instead of numbers.

hope this helps

regards

Marco

[Anonymous]

Thank you, Sunny...It's just what I exactly need....

[Anonymous]

Marco,

Thank you for the detailed explanation...I wish I can mark both correct answers for both of you and Sunny 🙂