Qlik Community

QlikView Connectors

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.

rvazquez
New Contributor II

sql select lost sign of a field

Hi!!

I need help, I need to make a sql select throught SQL SAP Connector to a SAP table.  In SAP table there are numbers with sign minus, but when I get this values, they are without sign.

For example,

SAP Content:

Captura.PNG

Qlikview content:

Captura1.PNG

Column Puntos in Qlikview lost the sign.

My SQL Select is:

[ZPUNTOS_CLIENTE]:   // Puntos asignados a un cliente y a nivel de ticket

Load

   [MANDT] as [ZPUNTOS_CLIENTE.MANDT],

   [KUNNR] as [ZPUNTOS_CLIENTE.KUNNR],

   [PUNTOS] as [ZPUNTOS_CLIENTE.PUNTOS],

   [EUR_JER] as [ZPUNTOS_CLIENTE.EUR_JER],

   [FECHA] as [ZPUNTOS_CLIENTE.FECHA],

   [PUNTOS_GASTADOS] as [ZPUNTOS_CLIENTE.PUNTOS_GASTADOS],

   [TICKET] as [ZPUNTOS_CLIENTE.TICKET];

SQL Select MANDT KUNNR TICKET PUNTOS FECHA EUR_JER PUNTOS_GASTADOS  from ZPUNTOS_CLIENTE

;

STORE * FROM [ZPUNTOS_CLIENTE] INTO ZPUNTOS_CLIENTE.QVD;

DROP TABLE [ZPUNTOS_CLIENTE];

Would you know anyone tell me why this error occurs?

Thanks.

1 Solution

Accepted Solutions
rvazquez
New Contributor II

Re: sql select lost sign of a field

Hi all,

finally I found the solution, I was using Qlikview Desktop 11.20 and for some reason it ignores minus sign at the end of the number except if I do:

text([PUNTOS_GASTADOS]) as [ZPUNTOS_CLIENTE.PUNTOS_GASTADOS]

then I format this text as:

num#(ZPUNTOS_CLIENTE.PUNTOS_GASTADOS,'#.##0')

finally I sum points correctly.

Thank you all for your help.

5 Replies
giakoum
Honored Contributor II

Re: sql select lost sign of a field

Most likely QV does not recognize the format from SAP. Try to format the number in the select statement (by using to_char?) so that the minus sign goes in front of the number, maybe QV will recognize that easier.

rvazquez
New Contributor II

Re: sql select lost sign of a field

Thank you loannis.  I tried to format the number in the select statement but when I executed it fails, I don't know what would be the correct sentence.

Otherwise, this field in SAP has set "Flag for sign in numerical fields"

Captura.PNG

I tried to uncheck this flag and select works perfectly, but I can't do this because program in SAP doesn't work.

Would someone know how can I fix this problem?

Thanks in advance.

Not applicable

Re: sql select lost sign of a field

Hi,

There must be some ABAP code written or transformation done in SAP.

Please check the same from SAP consultant and apply the same in qlikview.

Regards,

Neha Rangari

giakoum
Honored Contributor II

Re: sql select lost sign of a field

did you try something like :

select to_number(to_char(Puntos, '9999.99'), '9999.99')  from ZPUNTOS_CLIENTE

rvazquez
New Contributor II

Re: sql select lost sign of a field

Hi all,

finally I found the solution, I was using Qlikview Desktop 11.20 and for some reason it ignores minus sign at the end of the number except if I do:

text([PUNTOS_GASTADOS]) as [ZPUNTOS_CLIENTE.PUNTOS_GASTADOS]

then I format this text as:

num#(ZPUNTOS_CLIENTE.PUNTOS_GASTADOS,'#.##0')

finally I sum points correctly.

Thank you all for your help.