Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

nehasaxena
Not applicable

How to shift minus sign from right side to left side


Hello,

There are minus signs are on right side in the excel file that I am receiving from database team.

For example - 1.333-

Please help me to fix this in Qlikview.

Thanks.

Neha

10 Replies
balrajahlawat
Not applicable

Re: How to shift minus sign from right side to left side

Does every field value are like this?

Could you share a sample?

balrajahlawat
Not applicable

Re: How to shift minus sign from right side to left side

Try sth like this at script?

=SubField('1.333-','-',1)*-1

or

=SubField('YourField','-',1)*-1

tamilarasu
Not applicable

Re: How to shift minus sign from right side to left side

Try something like below,

Load

If(Right(A,1)='-',Right(A,1)&Left(A,Len(A)-1),A) as FieldName

From

File;

Replace A with your required field name.

marcus_sommer
Not applicable

Re: How to shift minus sign from right side to left side

You could use num#() - see: Reading SAP data that is positive and negative

- Marcus

PrashantSangle
Not applicable

Re: How to shift minus sign from right side to left side

Hi,

All value contain - at right then try

purgechar(FieldName,'-')*-1

if it contain +ve as well as -ve values then try

if(wildmatch(FieldNAme,'*-*'),purgeChar(FieldName,'-')*-1,FieldNAme)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
balrajahlawat
Not applicable

Re: How to shift minus sign from right side to left side

Marcus,

I tried like this but no change

=Num#('1.333-')  ///returning 1.333- only

tresesco
Not applicable

Re: How to shift minus sign from right side to left side

Num(Num#('1.333-'))  -> Returns :   -1.333

hirishv7
Not applicable

Re: How to shift minus sign from right side to left side

Hi,

Check this,

Data:

LOAD *,

'-'&SubField(Data,'-',1)  as ModData

INLINE [

Data

1.333-

];

Front end:

- sign.PNG

PFA,

Hope this helps,

Hirish

“Aspire to Inspire before we Expire!”
tresesco
Not applicable

Re: How to shift minus sign from right side to left side

=Num#('1.333-')  returns 1.333- which is actually dual number (it is internally -1.333). If you try applying any mathematical operation on it, you would see that. I.e

Num#('1.333-')*10  returns -13.33   correctly.

So solution (to see the number correctly), try:

Num(Num#('1.333-')) returns  -1.333