topic Re: Each Row Each Formula in QlikView

Each Row Each Formula

pgalvezt — Wed, 13 Apr 2016 21:20:50 GMT

Hello,

How in QlikView I can put a formula according the selection filter?

I have a filter with many values Each value has to have a formula. See the example for better understanding ...

Thank you!

Re: Each Row Each Formula

swuehl — Wed, 13 Apr 2016 22:23:52 GMT

If you want to evaluate a different expression per dimension line, you would need a conditional that checks the dimension value and branch into the appropriate expression path, e.g. using a pick/match conditional combination.

See attached a sample.

Re: Each Row Each Formula

pgalvezt — Thu, 14 Apr 2016 14:13:27 GMT

Hi swuehl ,

Thank you for your reply, Inside from inline table, How could I put a conditional formula from 2 differents resident tables?

See the attached test qvw file...

Valor| Pick( N°Expresion,If(TipoContrato = 'Hora',1.5*Sueldo*Hrs.Ext., If(TipoContrato = 'Día', 1.5*(SueldoMensual/[Horas Pactadas])*Hrs.Ext., 1.5*(Sueldo/30)*(7/45)*Hrs.Ext.))

SueldoMensual = From Different Table...

The other Table contains N°Semanas with another formula...

Re: Each Row Each Formula

swuehl — Thu, 14 Apr 2016 18:00:24 GMT

The expression must be a valid expression, so I would suggest that you double check by copying the expression to your straight table as expression.

You will see at least two issues

a) a missing closing bracket

b) missing aggregation function

Use Aggregation Functions!

Not sure how you want to aggregate your values, one possible expression could be

Pick( N°Expresion, Sum(If(TipoContrato = 'Hora',1.5*Sueldo*Hrs.Ext., If(TipoContrato = 'Día', 1.5*(SueldoMensual/[Horas Pactadas])*Hrs.Ext., 1.5*(Sueldo/30)*(7/45)*Hrs.Ext.))))

This expression is very costly to calculate, since QV needs to temporarily join all tables on the path between tables where the fields of your aggregation are located.

Then, the logic of the pick( N°Expresion, ...) would expect that you are listing (in your sample) 3 expressions, not only 1.

I assume you started with the first expression, then struggled with the syntax.

Hope this helps,

Stefan

Re: Each Row Each Formula

pgalvezt — Fri, 15 Apr 2016 14:06:09 GMT

Hi,

I found another way to get my result and almost I get it, just one expression gave me bad another result in comparison with the same expression but from the script :

If(TipoContrato = 'Hora',Hrs.Ord.*Sueldo, If(TipoContrato = 'Día', Ds.Trab.*Sueldo, Sueldo/[N° Semanas]))

I put the same expression and script: Watch sueldomensual Table (With Resident table)

I need to get the same value that my expression table graph....

Thanks!