Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Alternative to alt function

Hello!

FIELD1 and FIELD2 have not numeric values.

Aternatives?

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

Tags (2)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Alternative to alt function

Try

...

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

...

10 Replies
Not applicable

Re: Alternative to alt function

what are you trying to accomplish?  can you please provide more detail?

swuehl
Not applicable

Re: Alternative to alt function

If you want to check for NULL, you can use len(trim(FIELD)) = 0 or IsNull(FIELD).

sunny_talwar
Not applicable

Re: Alternative to alt function

Alt doesn't mind taking in alpha-numeric values if that's what you question is

maxgro
Not applicable

Re: Alternative to alt function

maybe istext

=if(IsText(yourfield), 'value for text', 'value for number or null')

Not applicable

Re: Alternative to alt function

Hello!

In this code(Author Master Sunny T):

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,

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

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;

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


If FIELD1 or FIELD1B are not numbers don`t work... and I read here: Alt function | Qlik Community Alt belongs to Conditional function (like extended IF function) returning first numerical value.

swuehl
Not applicable

Re: Alternative to alt function

Try

...

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

...

sunny_talwar
Not applicable

Re: Alternative to alt function

You are right. In essence I am checking (in simple english)

If FIELD1 is Not Null, then Pick FIELD1

Elseif FIELD1 is Null and FIELD2 is Not Null, then Pick FIELD2

Elsefil FIELD1 is Null and FIELD2 is Null and Value is not Null, then Pick Value

FIELD1FIELD2ValueFinal Output
10152010
Null202120
10Null1910
NullNull3030
10NullNull10

I think one change that you can make to the Alt function is that instead of using 2 Alts, you can do:

Alt(FIELD1, FIELD2, Value) -> Can't remember why I proposed 2 alts here

Not applicable

Re: Alternative to alt function

Thank you. I am learning a lot with the community and especially with you

Not applicable

Re: Alternative to alt function

This works for me .