Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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?

Tags (3)
8 Replies
Not applicable

Re: Problem with if expression

Give this a shot

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



Not applicable

Re: Problem with if expression

Sorry, invalid expression...

Thanks!

Not applicable

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?

Not applicable

Re: Problem with if expression

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;

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


MVP
MVP

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.


Not applicable

Re: Problem with if expression

Try with nested IF's  like below:

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

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

        )

   )

Not applicable

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

Thank you for your answers

Not applicable

Re: Problem with if expression

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
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.

Community Browser