Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

petrakostakova
New Contributor

Use set expression in load editor

Hello,

I use following expression in my app

Count( {$<[date.autoCalendar.Year]={$(=max(Year(date)))}>}distinct ID)

and it works fine, but I would like to define the set expression part in data load editor, because I reuse it many times and I want to export it easily.

I tried

Set

MaxYear= '[date.autoCalendar.Year]={$(=max(Year(date)))}'
in load editor and then use

Count( {$<$(=MaxYear)>}distinct ID)

but it does not work. The problem is with the inner function, which is not evaluated. Can anyone fix this problem?

Combination

Set

MaxYear='[date.autoCalendar.Year]={2015}'

and then

Count( {$<$(=MaxYear)>}distinct ID)

works fine, but I need to dynamically evaluate max(Year(date))

Thank you in advance!

Petra

1 Solution

Accepted Solutions

Re: Use set expression in load editor

The variable created in the SET will be incorrect because the $() will get substituted during the script run. You should be able to see this problem by looking at the variable in the variable overview.  To avoid the $() substitution in the script you have to use one of these trick techniques.

LET MaxYear= '[date.autoCalendar.Year]={$' & '(=max(Year(date)))}';


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

7 Replies
dwforest
Valued Contributor

Re: Use set expression in load editor

Something like:

Set MaxYear= '[date.autoCalendar.Year]={' & max(Year(date)) & '}'


Use the debug feature to make sure MaxYear evaluates to the string you're after...

petrakostakova
New Contributor

Re: Use set expression in load editor

It does not work... Like this the result is null...

I also tried

MaxYear= '[date.autoCalendar.Year]={' & $(=max(Year(date))) & '}

with the same result :-(

Re: Use set expression in load editor

The variable created in the SET will be incorrect because the $() will get substituted during the script run. You should be able to see this problem by looking at the variable in the variable overview.  To avoid the $() substitution in the script you have to use one of these trick techniques.

LET MaxYear= '[date.autoCalendar.Year]={$' & '(=max(Year(date)))}';


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

kvanandan1
Contributor

Re: Use set expression in load editor

Hi,

Please use

Let Max_date_yr = max(Year(date));

Let MaxYear = '[date.autoCalendar.Year]={$(Max_date_yr)}' ;

and then

Count( {$<$(=MaxYear)>}distinct ID)

petrakostakova
New Contributor

Re: Use set expression in load editor

Oh, thanks a lot. This works!

Thank you very much!

Petra

petrakostakova
New Contributor

Re: Use set expression in load editor

This does not help. But thanks for your suggestions!

oknotsen
Honored Contributor III

Re: Use set expression in load editor

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; if you can't see it, be sure to open the actual topic) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
Community Browser