Alternative to alt function

[#1]

Hello!

FIELD1 and FIELD2 have not numeric values.

Aternatives?

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

[#2]

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

[swuehl]

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

[sunny_talwar]

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

[maxgro]

maybe istext

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

[#6]

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 · Accepted solution]

Try

...

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

...

[sunny_talwar]

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

FIELD1	FIELD2	Value	Final Output
10	15	20	10
Null	20	21	20
10	Null	19	10
Null	Null	30	30
10	Null	Null	10

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

[#9]

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

[#10]

This works for me .