Condition of expression does not work

[#1]

Friends, good afternoon.

I'm having a problem with the condition of an expression.

The value in the database is with spaces to the right.
So I formulated this way:
If (CT2_DEBITO = '411110101 ', SUM (CT2_VALOR), 0)

But the result was 0, also tried using TRIM:
If (TRIM (CT2_DEBITO) = '411110101 ', SUM (CT2_VALOR), 0)

I am formulating a wrong expression? Or is something missing?

I appreciate the help.

[#2]

Try this

If (TRIM(CT2_DEBITO) = '411110101', SUM (CT2_VALOR), 0)

[#3]

I tried using TRIM, but failed.
Sorry, I messed up leaving an extra space, but had tried to TRIM.

Is there another way?

I appreciate the help!

[Miguel_Angel_Baeyens]

Hello,

In your load script, use

Text(CTA_DEBITO) AS CTA_DEBITO

So it's not taken as a number and it may be stored or displayed in some other way (using thousand or decimal separators, etc), and then use the trim code Érico posted above.

Hope that helps!

[#5]

Hi;

I suspect you have a problem well known maybe a bug. It's to display a 0 value in a chart.

Try this

DImension : CT2_DEBITO

Expressions

- = '' just to create a blank column.

- = If (CT2_DEBITO = '411110101', SUM (CT2_VALOR), 0) after loading the data with the text function as Miguel suggests

JJJ

[#6]

Well, I created a blank column and used the function Text (), but did not work.

When I create a list and select the tab above the specified value is associated
value.

I thank everyone for the tips.

[#7]

Maybe you could post your app ?

jjj

[#8]

Below is the script:

ODBC CONNECT TO ODBC_QLICKVIEW (XUserId is HBGGbZFEEE, XPassword is XcNTUYKGcBOMMcYMCDQOF);

SQL SELECT D2_COD, D2_TOTAL, D2_VALIPI, D2_CLIENTE AS CODCLI, D2_EMISSAO, MONTH(D2_EMISSAO) AS MES, YEAR(D2_EMISSAO) AS ANO, D2_VALICM + D2_VALIPI AS IMPOSTOS

FROM "Protheus10_Espelho".dbo.SD2020 D2 left outer join "Protheus10_Espelho".dbo.SF4020 F4

on F4_CODIGO = D2_TES

where D2_TIPO= 'N' AND F4_DUPLIC = 'S' AND D2.D_E_L_E_T_ = '' AND F4.D_E_L_E_T_ = '';

SQL SELECT A1_NOME AS NOME, A1_COD AS CODCLI FROM "Protheus10_Espelho".dbo.SA1020 WHERE D_E_L_E_T_ = '';

SQL SELECT F1_VALBRUT AS VALORBRUTO, MONTH(F1_EMISSAO) AS MES, YEAR(F1_EMISSAO) AS ANO FROM "Protheus10_Espelho".dbo.SF1020 WHERE D_E_L_E_T_ = '' AND F1_TIPO = 'D';

[Agenda]:

LOAD

*,

MONTH(CT2_DATA) AS MES,

YEAR(CT2_DATA) AS ANO,

Text(CT2_DEBITO) AS DEBITO;

SQL SELECT

*

FROM "Protheus10_Espelho".dbo.CT2020 WHERE D_E_L_E_T_ = '';

I'm using the version 9.

The data source is from an ERP(Microsiga).

[Anonymous · Accepted solution]

Maybe this is all you need:
sum(if(<condition>, <field>, 0))

[#10]

Michael Solomovich.

It worked, thank you.

Thank you all for your help.