topic strange behaviour with right function in load script in QlikView

strange behaviour with right function in load script

Thu, 06 Aug 2009 20:54:54 GMT

hello,

i have an issue in scripting using right function on my values always return negative sign -

My code is

[Code]LOAD @1 as %Key1,

@2 as %Key2,

@3 & '_' & @4 as %Key3,

@3 as %Key4,

@4 as %Key5,

if(index(@5,'-') <> 0 , 0-left(@5,len(@5)-1), @5 ) as [Value In Local Currency not bugged],

if(right(@6,1) = '-' , 0-left(@5,len(@5)-1), @5 ) as [Value In Group Currency bugged ]

FROM .\OHUB_YDCVARHIS_test.CSV (ansi, txt, delimiter is ';', no labels, msq);[/Code]

when i process my file :

9320;01000100;A2;00;716670.00;716670.00;716670.00;716670.00;EUR;EUR;2009003;K4

the result is always negative when i'm using the second formula 😞

Did i do something wrong ? or is this a known bug ?

thx for your replies

strange behaviour with right function in load script

Thu, 06 Aug 2009 21:22:12 GMT

I would replace the left(@5,len(@5)-1) syntax by NUM#(left(@5,len(@5)-1)) because you could have some strnage behavior ...

strange behaviour with right function in load script

Fri, 07 Aug 2009 13:39:57 GMT

so it means thatt i have to stop using right(...,1) ?

strange behaviour with right function in load script

Annette_Steinrücken — Fri, 07 Aug 2009 19:26:51 GMT

Hi,

you can use right, but to be sure to clean the string use trim() function

BR, Annette

strange behaviour with right function in load script

Mon, 10 Aug 2009 15:23:28 GMT

Thank you for the proposal, i tried but with no success 😞

if(right(trim(@5),1) = '-' , 0-left(@5,len(@5)-1), @5 )

Best regards

Philippe

strange behaviour with right function in load script

Annette_Steinrücken — Mon, 10 Aug 2009 16:13:21 GMT

Hi,

is it possible to post the application?

BR, Annette

strange behaviour with right function in load script

Mon, 10 Aug 2009 21:05:36 GMT

here it is

strange behaviour with right function in load script

Annette_Steinrücken — Tue, 11 Aug 2009 14:07:41 GMT

QV interprets the value in @5 as a number though your setting is a leading '-'.
That's why the right function does not work here. the number is -1420.76 in QV and has no '-' following.

Regards, Annette

strange behaviour with right function in load script

Tue, 11 Aug 2009 14:35:30 GMT

the first line is not the problem, look at the second one, the figure is positive nevertheless Qlikview show it as a negative one

regards

strange behaviour with right function in load script

Annette_Steinrücken — Tue, 11 Aug 2009 16:27:20 GMT

Yes, I understand.

The problem is that QV interprets @5 as a number. Even if you try to force QV to take it as a string with text()
Obviously there is a difference between left and right.

strange behaviour with right function in load script

vidyut — Tue, 11 Aug 2009 16:29:07 GMT

if(index(text(@5),'-') <> 0 , 0-left(text(@5),len(text(@5))-1), text(@5) ) as [Value In Local Currency not bugged],
if(right(trim(text(@5)),1) = '-' , 0-left(text(@5),len(text(@5))-1), text(@5) ) as [Value In Group Currency bugged ]

This works if you put Text(@5) instead of just @5