
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Alternative to alt function
Hello!
FIELD1 and FIELD2 have not numeric values.
Aternatives?
Alt(Alt(FIELD1, FIELD1B), Value) as FIELD1,
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try
...
If( Len(Trim(FIELD1)), FIELD1, If( Len(Trim(FIELD1B)), FIELD1B, Value)) as FIELD1,
...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
what are you trying to accomplish? can you please provide more detail?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want to check for NULL, you can use len(trim(FIELD)) = 0 or IsNull(FIELD).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Alt doesn't mind taking in alpha-numeric values if that's what you question is

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
maybe istext
=if(IsText(yourfield), 'value for text', 'value for number or null')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try
...
If( Len(Trim(FIELD1)), FIELD1, If( Len(Trim(FIELD1B)), FIELD1B, Value)) as FIELD1,
...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. I am learning a lot with the community and especially with you

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This works for me .

- « Previous Replies
-
- 1
- 2
- Next Replies »