Problem using IF Statement

[eduardo_dimperio]

Hi !

For some reason when a cut and paste a "IF" that's works on chart, its doesn't work on my script. I get "Invalid Expression" message and i don't find the error.

Any Help Please

DEFEITO:

LOAD

IF(Ultimadataleitura<today()-1 OR isnull(Ultimadataleitura) and statusConexao=0, OID_SYSTEM) AS OID_SYSTEM,

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC AND COUNT(DISTINCT CONCENTRATOR)>1,'SUM(s) COM PROBLEMA PONTUAIS HÁ MAIS DE 5 DIAS',

IF(COUNT (DISTINCT OID_UC)=QTDE_UC,'PREDIO INTEIRO COM PROBLEMAS',

IF(COUNT(DISTINCT CONCENTRATOR)=1,'SUM(s) '&CONCENTRATOR&' COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS ',

IF(COUNT(DISTINCT OID_UC)<QTDE_UC,'SUM(s) COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS','TODAS SUMs COM PROBLEMA HÁ MAIS DE 5 DIAS - VERIFICAR TELEMETRIA'

    )

    )

    )

    )

    ) AS LAUDO

[jonvitale]

Is there more to your script? There should be something after the Load statement to tell us where you are loading from, such as an external csv file or a previously loaded Resident table.

[Anonymous]

In the script if you use an aggregation function like max then there needs to be a Group By in the load statement.

I guess that may be the issue.

[eduardo_dimperio]

Hi Bill, that was my fist thought.

But this works:

DEFEITO:

LOAD

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<0,1,0

    )

    ) AS LAUDO

So Count and Max works inside this IF

And another strange thing

If i put any other line of code inside this table like this:

DEFEITO:

LOAD

IF(Ultimadataleitura<today()-1 OR isnull(Ultimadataleitura) and statusConexao=0, OID_SYSTEM) AS OID_SYSTEM,

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<0,1,0

    )

    ) AS LAUDO

Or change the IF results like this

DEFEITO:

LOAD

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC,1,0

    )

    ) AS LAUDO

I get the same invalid expression message

[Anonymous]

Could you share the full load statement including the Group By clause.

[eduardo_dimperio]

Hi Bill,

Follow the full code

[eduardo_dimperio]

Indeed Jonathan,

My fault.

DEFEITO:

LOAD

if(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

If(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC AND COUNT(DISTINCT CONCENTRATOR)>1,'SUM(s) COM PROBLEMA PONTUAIS HÁ MAIS DE 5 DIAS',

if(COUNT (DISTINCT OID_UC)=QTDE_UC,'PREDIO INTEIRO COM PROBLEMAS',

if(COUNT(DISTINCT CONCENTRATOR)=1,'SUM(s) '&CONCENTRATOR&' COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS ',

if(COUNT(DISTINCT OID_UC)<QTDE_UC,'SUM(s) COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS','TODAS SUMs COM PROBLEMA HÁ MAIS DE 5 DIAS - VERIFICAR TELEMETRIA'

     )

     )

     )

     )

    ) AS LAUDO

RESIDENT AUX;

DROP TABLE AUX;

[jonvitale]

@Eduardo

I think your problem (or one problem) is in the  fourth If statement:

     if(COUNT(DISTINCT CONCENTRATOR)=1,'SUM(s) '&CONCENTRATOR&' COM

"CONCENTRATOR" is a field with multiple values (I assume). You are checking to see if there is only one distinct value and then you hope to use that value in the resulting text. But, even though YOU know that CONCENTRATOR can be only one thing, the compiler doesn't. It thinks you are trying to stick an entire set of field names in a string.

Try

Only(CONCENTRATOR) to let the compiler know that you are looking for a single value.

I also think you probably need to use a Group by clause. If you want the entire data to be your group you could make a column with a single value ("1 as MyGroup") and then group by that.

Jonathan

[eduardo_dimperio]

Hi Jonathan unfortunately like i said before,this code give me error too:

DEFEITO:

LOAD

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC,1,0

    )

    ) AS LAUDO

But i'll try to use some group by and see what happen.

Thank you for your time

[jonvitale]

No, now I'm pretty sure Group By won't help you. When you use an aggregate function without a Group By clause, it assumes that your group is the whole data, and then it tries to return just one row. When you mix this with multiple row data it throws an error because you are working at two levels of granularity.

Here's an example:

TA:

Load *

Inline

[

Index, Group, Value

    1, A, 100

    2, A, 100

    3, A, 100

];

TB:

Load

// Index,   <--- Including Index throws an error

IF(Count(distinct Value) = 1, Only(Value), Null()) as ValueDistinct

Resident TA;

To create the "ValueDistinct", I am checking to see if there is only one distinct value of "Value". If so I return that value with the Only function, else I return Null. When loaded table TB will be a Single Row, because - presumably - using the Count function automatically aggregated on all of the data. Note, that if I try to use the Index field in TB I get an error because we'd be working at two different levels. Also, if you replace "Only(Value)" with a static value, such as 100, you'll get the same error. Just by calling Count in the conditional expression, you will aggregate to the group level (in this case all data).

I see what you are trying to do: you want to maintain all of your rows, but still use information from the entire data set to make decisions. Probably what you will need to do is make a reference table that includes the counts and max values that are of interest to you. Then use the peek function to compare each value in your complete table to those values in the reference table.

Here's an example, assuming I have TA from above:

Ref:

Load

Count(distinct Value) as NumDistinct

Resident TA;

TC:

NoConcatenate

Load

Index as NewIndex,

Peek(NumDistinct, -1, 'Ref') as NumDistinctPeek,

IF(Peek(NumDistinct, -1, 'Ref') = 1, Value, Null()) as ValueDistinct

Resident TA

In this case I am using the value from the "Ref" table (a count of distinct value) as a variable for comparison in the second table. The second table will now maintain all of my rows because there is no implied aggregation.

I hope this helps,

Jonathan