Set analysis in script editor

[HKP76]

Good morning everyone,

This is the first time I ask for help on this community.

I am trying to create a set analysis in my script editor but with no success. Here  below is my need:

I created a variable that counts the volume of my assets based on a key, that key changing depending on the way I want my volume counted. This works fine. But now I created a second variable, to sort based on the highest volume of the last year in my data table. And this second part does not work.

Here below is my script. Thanks for your help,

test:

NoConcatenate
Load
* INLINE [
ID,VOLUME_CALCULATION,TYPE_COUNT,TYPE_SORT
'1','Method_1','count(distinct(Key_1))','count({<YR={"$(=max({<YR>}YR))"}>}distinct(Key_1))'
'2','Method_2','count(distinct(Key_2))','count({<YR={"$(=max({<YR>}YR))"}>}distinct(Key_2))'
] (delimiter is ',');

LOAD Concat(TYPE_COUNT, ',', ID) As ConcatExpression ,
Concat(TYPE_SORT, ',', ID) As ConcatExpression2
Resident test;

Let vConcatExpressions = 'Pick(ID,' & Peek('ConcatExpression') & ')';
Let vConcatExpressions2 = 'Pick(ID,' & Peek('ConcatExpression2') & ')';

I use $(VconcatExpressions) to calculate the volume, it works fine, but $(VconcatExpressions2) does not work.

I know that when I used directly count({<YR={"$(=max({<YR>}YR))"}>}distinct(Key_1)) in my expression field it works fine.

I used several combination of ',",$,= but with no success...

Thank you very much for your help,

[sunny_talwar · Accepted solution]

Try this script

test:
LOAD Concat(TYPE_COUNT, ',', ID) As ConcatExpression,
	 Replace(Concat(TYPE_SORT, ',', ID), '@', '$') As ConcatExpression2;
LOAD * INLINE [
    ID, VOLUME_CALCULATION, TYPE_COUNT, TYPE_SORT
    1, Method_1, count(distinct(Key_1)), 'count({<YR={"@(=max({<YR>}YR))"}>}distinct(Key_1))'
    2, Method_2, count(distinct(Key_2)), 'count({<YR={"@(=max({<YR>}YR))"}>}distinct(Key_2))'
];

LET vConcatExpressions = 'Pick(ID,' & Peek('ConcatExpression') & ')';
LET vConcatExpressions2 = 'Pick(ID,' & Peek('ConcatExpression2') & ')';

Fact:
LOAD ID,
	 YR,
	 If(Len(Trim(Key_1)) > 0, Key_1) as Key_1,
	 If(Len(Trim(Key_2)) > 0, Key_2) as Key_2;
LOAD * INLINE [
    ID, YR, Key_1, Key_2
    1, 2018, 123, 1232
    1, 2018, 231, 1445
    1, 2019, 234, 2352
    1, 2019, 142, 5632
    2, 2018, 135
    2, 2018, 242, 5234
    2, 2018, 545, 2134
    2, 2019, , 2432
];

and then this

$(vConcatExpressions)
$(vConcatExpressions2)

[HKP76]

Dear Sunny; it is working like a charm, thank you for your help.

Just for my understanding, why was the $ the issue?

[sunny_talwar]

Qlik script tries to execute the dollar sign expansion.