8 Replies Latest reply: Jan 18, 2016 2:14 PM by Roger Grossi

# Problem with if expression

Hello,

my expression:

If((Min(MONTHYEAR) > Min(MONTHYEAR2)) and len(trim(FIELD1)<>0), Only(FIELD1B),FIELD1) as Value

What I doing wrong?

• ###### Re: Problem with if expression

Give this a shot

If((Min(MONTHYEAR) > Min(MONTHYEAR2)) and len(trim(FIELD1))>0, Only(FIELD1B),FIELD1) as Value

• ###### Re: Problem with if expression

Sorry, invalid expression...

Thanks!

• ###### Re: Problem with if expression

Hi,

Try the expressions separately without the AND condition and check it out. If everything works fine then move ahead with AND condition. Could you post your sample app?

• ###### Re: Problem with if expression

Code:

Table:

If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,

MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2

Inline [

ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

2,2,2005,10,B,3,2005,40

2,5,2005,40,D,,,40

23,11,2008,25,C,,,30

23,2,2009,30,H,,,30

100,,,,,6,2010,555

500,3,2015,80,X,1,2015,58

500,6,2015,60,W,,,58

333,4,2014,100,AA,8,2014,100

333,6,2014,300,BB,,,100

];

Join (Table)

Value,

If(Max(MONTHYEAR) < Max(MONTHYEAR2), Alt(Max(MONTHYEAR2), Max(MONTHYEAR)), Max(MONTHYEAR)) as Max,

If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,

//I Want to change this expression

If(Max(MONTHYEAR) < Max(MONTHYEAR2) or Min(MONTHYEAR) > Min(MONTHYEAR2), Only(FIELD1B)) as Value

//HERE I NEED:

/*

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1),FIELD1B,if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(FIELD1)>0,FIELD1)) as Value

I need this:

1.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1) output FIELD1B

2.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnotnull(FIELD1) output FIELD1

*/

Resident Table

Group By ID;

FinalTable:

NoConcatenate

MONTH,

YEAR,

MONTHYEAR,

Alt(Alt(FIELD1, FIELD1B), Value) as FIELD1,

FIELD2;

Num(Month(MONTHYEAR)) as MONTH,

Year(MONTHYEAR) as YEAR,

MONTHYEAR,

// MONTH2,

// YEAR2,

FIELD1B,

Value,

If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,

If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2

Resident Table

Order By ID, MONTHYEAR;

DROP Table Table;

//////////////////////

• ###### Re: Problem with if expression

You need aggregation functions for all fields that you don't list in your GROUP BY clause:

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(Only(FIELD1))>0,Only(FIELD1))) as Value

or maybe just

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),Only(FIELD1) ) as Value

Not sure if this is what you want, though. Probably Sunny can help you with the logic.

• ###### Re: Problem with if expression

With this inline table:

Inline [

ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

1000,3,2015,rev,bb,10,2014,soul

1000,5,2015,soul,rev,10,2014,soul

555,3,2015,50,40,2,2015,80

555,4,2015,80,50,2,2015,80

100,,,,,6,2010,555];

IF MONTH2<MONTH AND FIELD2 IS NOT NULL THEN FINAL_FIELD1=FIELD2

IF MONTH2<MONTH AND FIELD2 IS NULL THEN FINAL_FIELD1=FIELD1B

I only use FIELD1B if month2 and year2 are <month and year and FIELD2 is null

but i always use month2 and year2 for the table

Output:

ID,MONTH,YEAR,FINAL_FIELD1,FINAL_FIELD2

1000,10,2014,bb,-

1000,11.2014,bb,-

1000,12,2014,bb,-

1000,1,2015,bb,-

1000,2,205,bb,-

1000.3,2015,rev,bb

1000,4,2015,rev,bb

1000,5,2015,soul,rev

555,2,2015,40,-

555,3,2015,50,40

555,4,2015,80,50

100,6,2010,555

• ###### Re: Problem with if expression

My code now:

Table:

If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,

MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2

Inline [

ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

1000,3,2015,rev,bb,10,2014,soul

1000,5,2015,soul,rev,10,2014,soul

555,3,2015,50,40,2,2015,80

555,4,2015,80,50,2,2015,80

100,,,,,6,2010,555];

Join (Table)

Value,

If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(Only(FIELD1))>0,Only(FIELD1))) as Value

Resident Table

Group By ID;

FinalTable:

NoConcatenate

Value,

MONTH,

YEAR,

MONTHYEAR,

If( Len(Trim(FIELD1)), FIELD1, If( Len(Trim(FIELD1B)), FIELD1B, Value)) as FIELD1,

FIELD2;

Num(Month(MONTHYEAR)) as MONTH,

Year(MONTHYEAR) as YEAR,

MONTHYEAR,

FIELD1B,

Value,

If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,

If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2

Resident Table

Order By ID, MONTHYEAR;

DROP Table Table;

But the output is:

ID MONTH YEAR FIELD1 FIELD2 MONTHYEAR Value
5552201580 Feb 201580
555320155040Mar 201580
555420158050Apr 201580
555520158050May 201580
555620158050Jun 201580
555720158050Jul 201580
555820158050Aug 201580
555920158050Sep 201580
5551020158050Oct 201580
5551120158050Nov 201580
5551220158050Dec 201580

And I need:

Output:

ID,MONTH,YEAR,FINAL_FIELD1,FINAL_FIELD2

1000,10,2014,bb,-

1000,11.2014,bb,-

1000,12,2014,bb,-

1000,1,2015,bb,-

1000,2,205,bb,-

1000.3,2015,rev,bb

1000,4,2015,rev,bb

1000,5,2015,soul,rev

555,2,2015,40,-

555,3,2015,50,40

555,4,2015,80,50

100,6,2010,555

I think the problem happens when I have MONTH2 and YEAR2 < MONTH and YEAR, and I have a FIELD1 and FIELD2. I need put FIELD2(or FIELD1, it´s no problem) in the previous date.

Thank you.

• ###### Re: Problem with if expression

Try with nested IF's  like below:

IF( Min(MONTHYEAR) > Min(MONTHYEAR2) ,

IF( Len(Trim(FIELD1)) , FIELD1 , FIELD1B

)

)