Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
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
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..
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.
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
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 !