Set Analysis - IF on a non aggregation function

[#1]

Hello experts

First of all, I am kind of new in Qlik Sense, so apologies in advance if this is something pretty obvious.

I am creating an app to analyse invoice data. There are two different date fields in the data model, one of them has many NULL values (Date1) and the other hasn't (Date2).

What I am trying to do is to create a unique field date which takes the value from Date2 field in case it is not NULL, otherwise it should take the date from Date1, which has value in every record.

I am trying to do it directly in the table graph by setting this function as a measure:

IF([Date1]=' ', [Date2],[Date1])

But the result is not as nice as I expected, because it is working just fine for records where Date2 is not NULL, as it gets the Date2 value in those cases. But in the rest of records is not displaying any value at all, so basically what is doing this function is copying Date2 column.

I am trying to fix the function, but all the IF function examples in set analysis that I have been able to find by searching in google so far, are aggregation function related. Therefore I am kind of clueless.

Thanks in advance and regards

[swuehl · Accepted solution]

Maybe try

=IF(Len(Trim([Date1]))=0, [Date2],[Date1])

if you are using this in a chart expression, consider using aggregation functions!

Use Aggregation Functions!

[sunny_talwar]

Try this with Alt:

Alt(Date2, Date1)

Alt(Date1, Date2)

[#4]

Hello swuehl and Sunny T

First of all, lot of thanks for your help and your replies, both of them have lead me into the desired result. I am marking swuehl's as the correct one because he commented first, but the two answers were hugely useful to me.

Thanks again and regards

[sunny_talwar]

Javier -

AFAIK we don't worry about who gets the correct answer as long as one or both of us is able to help you attain what you wanted.

Best,

Sunny

[#6]

Both of you have provide me an incredible help, because not only you have solved my problem, but you have also taught me things that I didn't know, like the 'Alt' function and the good practice of using aggregation functions (which also explains why all the IF examples of set analysis that I had been able to find had aggregation functions), this is really great and I am very grateful!

Regards