topic Re: subfield with negative values in QlikView

subfield with negative values

shree909 — Tue, 09 Apr 2013 02:22:11 GMT

Hi,

I am trying to understand the subfield(fieldname,'\', -1) what does the -1 values mean when i am uisng script..

when i am having year as a field name, when i use the above function in script it sorts the order by desc to ascending..

i am confused what it will return the value when used in load script..

Thanks

Sri

Re: subfield with negative values

Anonymous — Tue, 09 Apr 2013 03:11:50 GMT

If the value in your fieldname is for example: 'one;two;three' and the expression is:

subfield(fieldname, ';', -1)

Index Returned

1 one

2 two

3 three

0 null()

-1 three

-2 two

-3 one

Re: subfield with negative values

Tue, 09 Apr 2013 03:59:33 GMT

Hi ,

You can refer Reference manual page no 323 .

There you can find sufficient one to understand .

If you use -1 in Subfield it returns the Last value of the string after the Delimiter '\' .

Re: subfield with negative values

sushil353 — Tue, 09 Apr 2013 04:46:25 GMT

hi,

-1 identified to have the last value in the string

suppose you have a datafield like 2013\02\03

so your above function will return 03

if you use subfield(datafield,'\', 1) then it will return 2013

I didn't understand your statement:when i am having year as a field name, when i use the above function in script it sorts the order by desc to ascending..

HTH

Sushil

Re: subfield with negative values

alexandros17 — Tue, 09 Apr 2013 06:54:50 GMT

-1 means the last string in the seguence

so if you have aaa,bbb,ccc,ddd

-1 extract ddd, -2 is ccc and so on

hope it helps

Re: subfield with negative values

Tue, 09 Apr 2013 08:20:57 GMT

Hi Sri,

as far as i remember, index should be positive

subfield function will return the index position of fieldname

regards

chris

Re: subfield with negative values

Tue, 09 Apr 2013 09:09:07 GMT

It always mean subtext after last delimeter (or first subtext from right to left).

For example,
if you have Fieldname = '12\03\2012', subfield(fieldname,'\', -1) = '2012'

if you have Fieldname = '12\03', subfield(fieldname,'\', -1) = '03'

What do you want to reach with using this function?

Re: subfield with negative values

swuehl — Tue, 09 Apr 2013 09:42:22 GMT

A negative index (third parameter to subfield() function) should count and extract your field portion from the end of the string:

subfield('Begin\End','\',1)

will return 'Begin'

subfield('Begin\End','\',-1)

will return 'End'.

Re: subfield with negative values

Tue, 09 Apr 2013 11:34:28 GMT

Hi,

subfield(text, 'delimiter' [,n] )

-it splits s after delimiter and takes the n'th split part

-if n is negative then it will result the n'th split part from reverse (absolute of n)

exemple given:

subfield('20\07\2005','\',2)='07'

subfield('20\07\2005','\', 4)='' (empty string)

subfield('20\07\2005','\', -1)='2005'

subfield('20\07\2005','\', -3)='20'

subfield('a;b;c;d;e;f;g;h;i;j;k;l;m' , ';' , -3)='k' (3 rd position from reverse)

Re: subfield with negative values

jagan — Tue, 09 Apr 2013 11:58:13 GMT

Hi,

Check the script below

Test:

LOAD

subfield(F1,',', -1) as F1;

LOAD * INLINE [

"10,20,30,40"

];

If you -1 in the Subfield, then you will get 40 in F1, when negative number it gets the value from right.

If you -2 in the Subfield, then you will get 30 in F1, when negative number it gets the value from right.

If you 1 in the Subfield, then you will get 10 in F1, when positive number it gets the value from left.

If you 2 in the Subfield, then you will get 20 in F1, when positive number it gets the value from left.

Hope this helps in understanding the SubField().

Regards,

Jagan.