topic Re: Alternative to alt function in QlikView

Alternative to alt function

Fri, 15 Jan 2016 13:56:06 GMT

Hello!

FIELD1 and FIELD2 have not numeric values.

Aternatives?

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

Re: Alternative to alt function

Fri, 15 Jan 2016 17:29:20 GMT

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

Re: Alternative to alt function

swuehl — Fri, 15 Jan 2016 17:32:45 GMT

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

Re: Alternative to alt function

sunny_talwar — Fri, 15 Jan 2016 17:36:26 GMT

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

Re: Alternative to alt function

maxgro — Fri, 15 Jan 2016 18:35:59 GMT

maybe istext

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

Re: Alternative to alt function

Sun, 17 Jan 2016 04:00:55 GMT

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.

Re: Alternative to alt function

swuehl — Sun, 17 Jan 2016 14:21:06 GMT

Try

...

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

...

Re: Alternative to alt function

sunny_talwar — Sun, 17 Jan 2016 14:25:59 GMT

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

Re: Alternative to alt function

Mon, 18 Jan 2016 09:21:10 GMT

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

Re: Alternative to alt function

Mon, 18 Jan 2016 09:24:56 GMT

This works for me .

Re: Alternative to alt function

Mon, 18 Jan 2016 16:17:40 GMT

Relate:Problem with if expression

Re: Alternative to alt function

AlexOmetis — Tue, 06 Jul 2021 12:56:02 GMT

Just to update this old post... there is now the COALESCE function that is like ALT but for strings...