Problem with if expression

[#1]

Hello,

my expression:

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

What I doing wrong?

[#2]

Give this a shot

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

[#3]

Sorry, invalid expression...

Thanks!

[#4]

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?

[#5]

Code:

Table:

LOAD *,

  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)

LOAD ID,

  Value,

  MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR

While AddMonths(Min, IterNo()) <= Max;

LOAD ID,

  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

LOAD ID,

  MONTH,

  YEAR,

  MONTHYEAR,

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

  FIELD2;

LOAD ID,

  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;

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

[swuehl]

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.

[#7]

Try with nested IF's  like below:

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

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

        )

   )

[#8]

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

Thank you for your answers

[#9]

My code now:

Table:

LOAD *,

  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)

LOAD ID,

  Value,

  MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR

While AddMonths(Min, IterNo()) <= Max;

LOAD ID,

  MonthName(addmonths(today(),-0)) as Max,

  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

LOAD ID,

Value,

  MONTH,

  YEAR,

  MONTHYEAR,

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

  FIELD2;

LOAD ID,

  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
555	2	2015	80		Feb 2015	80
555	3	2015	50	40	Mar 2015	80
555	4	2015	80	50	Apr 2015	80
555	5	2015	80	50	May 2015	80
555	6	2015	80	50	Jun 2015	80
555	7	2015	80	50	Jul 2015	80
555	8	2015	80	50	Aug 2015	80
555	9	2015	80	50	Sep 2015	80
555	10	2015	80	50	Oct 2015	80
555	11	2015	80	50	Nov 2015	80
555	12	2015	80	50	Dec 2015	80

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.