topic Dynamic expression in QlikView

Dynamic expression

Fri, 09 Jul 2010 12:41:53 GMT

Very simpel written:

In the very same column, if Category is 'Dogfood' I want to calculate Sales_index_vs_last_year. If Category is 'Furniture' I want to calculate Sales_Index_vs_budget. (Category is just an exampel - the file and problem is about economics).

With a very big dataset I do not want to use macro, If, Pick or Multiplication.

I found almost same issue in http://community.qlik.com/forums/t/25527.aspx but that did not solve my case.

In the attached file, I have a deeper explanation, and a solution that does not fully work. Here the expression is defined in a table.

Any ideas or solutions?

Best regards, Jerrik

Dynamic expression

Fri, 09 Jul 2010 18:02:05 GMT

It's not possible to do this by dollar sign expression when you have more than one value for the field at a time. You have to use IF condition. See my example in attachment. Your expression is working if you pick just one value from the field SalesLastYear_Budget.

Dynamic expression

Fri, 09 Jul 2010 22:00:24 GMT

Thx for your reply. I know this, and I have tried the Pick command as well. Both work, but it consumes twice og three times as much CPU as the simple calculation.

My conclusion is that the validation is very expensive, and since my dataset is plus 100 mill rows, performance is an issue.

Actually I think that there is just one expression per row in the table, since the DriftRapNr is showed in the table. If I just could get the software to 'realise' that....

It is not a criteria itself, to the expression is defined in a table. But I see it as a possibility to avoid validating which expression to use.