topic Re: Subfield Issue in QlikView

Subfield Issue

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

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

Re: Subfield Issue

vishsaggi — Mon, 30 Oct 2017 21:48:43 GMT

Use like

subfield(Material, '-', 5)

Re: Subfield Issue

Anonymous — Mon, 30 Oct 2017 22:05:44 GMT

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.

Re: Subfield Issue

vishsaggi — Mon, 30 Oct 2017 22:58:43 GMT

IS SY common in all the values you get ?

Re: Subfield Issue

sunny_talwar — Mon, 30 Oct 2017 23:15:28 GMT

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

];

Re: Subfield Issue

MarcoWedel — Mon, 30 Oct 2017 23:30:41 GMT

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

Re: Subfield Issue

MarcoWedel — Mon, 30 Oct 2017 23:41:35 GMT

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

Re: Subfield Issue

Anonymous — Tue, 31 Oct 2017 21:22:11 GMT

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

Re: Subfield Issue

Anonymous — Tue, 31 Oct 2017 21:23:47 GMT

Marco,

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