topic Re: Set and set analysis in QlikView

Set and set analysis

salezian — Fri, 19 Jan 2018 12:58:30 GMT

Dear Qlik Gurus,

I wanted to use following statement in script

set vMaxDate='[Reference Date]={"$(=max([Reference Date]))"}';

to use variable in set analysis.

The weird thing is that the resulting value of the variable after reloading is

[Reference Date]={""}

I completely don't understand why. I always thought that set stores values literally, but it looks like Qlik is trying to execute the function max.

Thanks in advance

Anil_Babu_Samineni — Fri, 19 Jan 2018 13:05:32 GMT

How your set expression looks like in UI?

jonathandienst — Fri, 19 Jan 2018 13:08:54 GMT

Because the $() expansion executes before the Set statement. You need to defer the expansion, like

Let vMaxDate = '[Reference Date]={"$' & '(=max([Reference Date]))"}';

jcampbell474 — Fri, 19 Jan 2018 13:11:10 GMT

You can try calling the max reference date first, then peek at the value.

TempMaxDate:

LOAD

max([Reference Date]) as maxRefDate

Resident [yourtable];

Let vMaxRefDate=peek('maxRefDate');

Drop Table TempMaxDate;

(You may need to throw some date formatting in there - depending on your data.)

salezian — Mon, 22 Jan 2018 14:17:08 GMT

Thanks Jonathan. That was it. I've used replace function

set vMaxDate = '[Reference Date]={"@(=max([Reference Date]))"}';

let vMaxDate =replace(vMaxDate,'@',$')

All the best.