Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Not applicable

## Alternative to alt function

Hello!

FIELD1 and FIELD2 have not numeric values.

Aternatives?

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

1 Solution

Accepted Solutions
MVP

Try

...

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

...

11 Replies
Not applicable
Author

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

MVP

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

MVP

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

MVP

maybe istext

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

Not applicable
Author

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.

MVP

Try

...

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

...

MVP

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
Author

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

Not applicable
Author

This works for me .

Community Browser