topic Re: Problem with if expression in QlikView

Problem with if expression

Mon, 18 Jan 2016 12:06:24 GMT

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

Mon, 18 Jan 2016 16:04:13 GMT

Give this a shot

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

Re: Problem with if expression

Mon, 18 Jan 2016 16:05:35 GMT

Sorry, invalid expression...

Thanks!

Re: Problem with if expression

Mon, 18 Jan 2016 16:10:31 GMT

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

Mon, 18 Jan 2016 16:16:16 GMT

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;

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

Re: Problem with if expression

swuehl — Mon, 18 Jan 2016 18:20:46 GMT

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

Mon, 18 Jan 2016 18:50:01 GMT

Try with nested IF's like below:

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

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

)

Re: Problem with if expression

Mon, 18 Jan 2016 18:53:34 GMT

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

Re: Problem with if expression

Mon, 18 Jan 2016 19:14:40 GMT

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.