topic Re: custom table - formulas in xls in QlikView

custom table - formulas in xls

Tue, 30 Aug 2016 04:30:51 GMT

Hi Guys/gourous/newbie,

I do have a challenge (I know it's feasible, I did it long time ago but my brain is rusty... dammed !! I don't age well)

I need to build a table base on a really particular format.

The formatting and the formulas are both stored in an xls.

Xls in input (i'm not sure if the formulas have to start by '=' or not.. so I tried both)

My challenge is to be able to interprète the formulas in my table in my table. I get them as a string or the interpretation works when I select 1 single Row Label. It's disappearing when you get more than one.

I tried different formulas :

=FirstSortedValue(Formula,RowID) -- give the formula as a string

$(=FirstSortedValue(Formula,RowID)) --interprete the formula for 1 single rowLabel selected

Please any help?

Thanks a lot ,

Cheers

Re: custom table - formulas in xls

sushil353 — Tue, 30 Aug 2016 04:43:39 GMT

Hi,

Check this out: Store expression into excel sheet and re-use expressions in many charts using variables

HTH

Re: custom table - formulas in xls

tresB — Tue, 30 Aug 2016 04:45:37 GMT

AFAIK, this is a known limitation. I am not sure if am being wrong saying this a 'limitation'. But yes, it doesn't work the way we want as you described above (Only works when you select to reduce to single row). As an alternate way, you can consider using variables as explained here: Set Analysis in Expression (Chart)?

Re: custom table - formulas in xls

Tue, 30 Aug 2016 05:12:00 GMT

Hi Sushil,

Thanks for your answer as the number of rows will probably change, I tried to avoid to go though variables. (less maintenable).

@Amit, I'm sure I made it working 2 years ago for a client :S

Re: custom table - formulas in xls

vinieme12 — Tue, 30 Aug 2016 15:24:16 GMT

This might give you some insight

Loading Expressions From File - WITHOUT creating variables in Script

Re: custom table - formulas in xls

Peter_Cammaert — Tue, 30 Aug 2016 16:36:49 GMT

If you intend to create a table in which every row has to show a different expression, then you're indeed hitting an annoying limitation of QlikView. Almost everyone has tried this when in need of a Metrics table and failed in exactly the same way. However, there are very logical reasons why this doesn't work without a workaround:

$-sign expressions are evaluated before a table is calculated. Therefor, the output cannot change from row to row.
In the UI, you cannot evaluate the output of another expression, like you can in a load script with the eval() function.
...

However, Ralf Becher once demonstrated a great technique that can be used as a workaround. It's based on the nested IF-THEN-ELSE trick. You will have to adapt it so that a single variable is created during a reload that checks every dimension value from your Excel and produces the corresponding formula for evaluation in the THEN parameter.

See here. Re: Re: Chart with count of $Field values‌

Because Metrics tables do not have large numbers of rows (usually), a giant IF-THEN-ELSE is ok here.

Best,

Peter