IF statement - can't copy a row if another row is Null() / =0

[Baptiste_Galaup]

Hello everyone.

I have this table :

As you see, when there is a value in column E, there is not in colum F, and vice versa.

I try to create a column who takes the value in E if there is nothing in F, and the value in F if there's nothing in E.

On Libre Office Calc, it's pretty simple (column G - formula at the top of the screen).

I tried multiple ways to do the same in Qlik and it doesn't work.

More specifically, Qlik find a value for the column I'm targeting in the first term of the IF statement, but not the second term. If I switch this, then it works only for the other column

I tried to the operators = Null(), and = 0 too

I tried to avg() each column, and to go avg(if(column E...)).

I tried a Dual IF, to cover all cases (=Null(), <>Null() for each column)

Can you see what I'm doing wrong ?

[PrashantSangle]

Not sure, but try below

if(avg([PRIX MWH NU])=0 or len(trim(avg([PRIX MWH NU])))=0 or isnull(avg([PRIX MWH NU])),avg([PRIX MWH ARENH]),avg())

Regards,

Prashant Sangle

[Digvijay_Singh]

May be like this, not pretty sure which one would suit here - 

Rangemax(Column(1),Column(2))

OR may be

Coalesce(Column(1),Column(2))

You may replace Column functions with actual measure expression as well..

[luizcdepaula]

You can try

If(isnull ([PRIX MWH NU]),avg([PRIX MWH ARENH]),avg([PRIX MWH NU])) 

or

Avg(alt([PRIX MWH ARENH],[PRIX MWH ARENH]))

The formula above will check for null values at the row level, not at the aggregated level.

[marcus_sommer · Accepted solution]

IMO it shouldn't be done in the UI else within the data-model, maybe with something like:

alt(Prix1, Prix2) as Prix

and if necessary the source might be also fetched, with:

pick(rangesum(sign(fabs(Prix1))*1, sign(fabs(Prix2))*2), 'Prix1', 'Prix2') as PrixType

- Marcus

[Baptiste_Galaup]

Oh boy, you all are right.

I post a screenshot of all the solutions applied to the data.

The data is well displayed in each solution but the only column whose Total is correct is the last.

<< alt(Prix1, Prix2) as Prix >> in the load script editor will do the trick.

Thank you all for your reactivity !