10 Replies Latest reply: Jan 18, 2016 11:17 AM by Roger Grossi

# Alternative to alt function

Hello!

FIELD1 and FIELD2 have not numeric values.

Aternatives?

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

• ###### Re: Alternative to alt function

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

• ###### Re: Alternative to alt function

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

• ###### Re: Alternative to alt function

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

• ###### Re: Alternative to alt function

Hello!

In this code(Author Master Sunny T):

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,

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

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;

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

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.

• ###### Re: Alternative to alt function

Try

...

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

...

• ###### Re: Alternative to alt function

This works for me .

• ###### 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

• ###### Re: Alternative to alt function

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

• ###### Re: Alternative to alt function

maybe istext

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