Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Variable not working

Hello,

 

I'm loading an INLINE table, from which I intend to use several variables, that will change based on choices :

 

vconcatExpressions: Allows me to calculate a volume

vconcatExpressions2: Allows me to sort my table by the largest volume of the last year in study

vconcatExpressions3: Allows me to create a row that is the ranking of that row within the sorting of vconcatExpressions2 and filter the number of values to display.

Choices: One type of Volume calculation (column 'COLUMN_CALCULATION') and one type of Data display (column 'TOP_SELECTION').

vconcatExpressions and vconcatExpressions2 are working fine, but not vconcatExpressions3.

However I know that it it not a problem of formula (column 'TOP_FILTER') because when I apply them one by one in the edit filed of the row , it is working just fine.

Thank you very much for your help,

 

Here below the part of my script:

 

Table_merged:
NoConcatenate
Load
* INLINE [
ID, KEY,VOLUME_CALCULATION,TOP_SELECTION,TYPE_COUNT,TYPE_SORT,TOP_FILTER
'1','By TC, Rule_11 Breakdown - Top 10','By TC, Rule_11 Breakdown','Top 10','count(distinct(RULE_11_COUNT))','count({<YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))','if(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)<11,(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)&'.'&REAL_LANE_POINT_NAME))'
'2','By TC, Rule_11 Breakdown - Top 20','By TC, Rule_11 Breakdown','Top 20','count(distinct(RULE_11_COUNT))','count({<YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))','if(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)<21,(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)&'.'&REAL_LANE_POINT_NAME))'
'3','By TC, Rule_11 Breakdown - All','By TC, Rule_11 Breakdown','All','count(distinct(RULE_11_COUNT))','count({<YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))','(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)&'.'&REAL_LANE_POINT_NAME)'
'4','By TC, No Breakdown - Top 10','By TC, No Breakdown','Top 10','count(distinct(NO_RULE_11_COUNT))','count({<YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))','if(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)<11,(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)&'.'&CLASSIC_LANE_POINT_NAME))'
'5','By TC, No Breakdown - Top 20','By TC, No Breakdown','Top 20','count(distinct(NO_RULE_11_COUNT))','count({<YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))','if(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)<21,(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)&'.'&CLASSIC_LANE_POINT_NAME))'
'6','By TC, No Breakdown - All','By TC, No Breakdown','All','count(distinct(NO_RULE_11_COUNT))','count({<YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))','(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)&'.'&CLASSIC_LANE_POINT_NAME)'
'7','By TEU, Rule_11 Breakdown - Top 10','By TEU, Rule_11 Breakdown','Top 10','count({<PACKAGE_CODE={"2*"}>} distinct(RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"}>} distinct(RULE_11_COUNT))*2','count({<PACKAGE_CODE={"2*"},YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"},YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))*2','if(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)<11,(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)&'.'&REAL_LANE_POINT_NAME))'
'8','By TEU, Rule_11 Breakdown - Top 20','By TEU, Rule_11 Breakdown','Top 20','count({<PACKAGE_CODE={"2*"}>} distinct(RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"}>} distinct(RULE_11_COUNT))*2','count({<PACKAGE_CODE={"2*"},YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"},YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))*2','if(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)<21,(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)&'.'&REAL_LANE_POINT_NAME))'
'9','By TEU, Rule_11 Breakdown - All','By TEU, Rule_11 Breakdown','All','count({<PACKAGE_CODE={"2*"}>} distinct(RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"}>} distinct(RULE_11_COUNT))*2','count({<PACKAGE_CODE={"2*"},YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"},YR={"@(=max({<YR>}YR))"}>} distinct(RULE_11_COUNT))*2','(aggr(Rank(@(vConcatExpressions2)), REAL_LANE_POINT_NAME)&'.'&REAL_LANE_POINT_NAME)'
'10','By TEU, No Breakdown - Top 10','By TEU, No Breakdown','Top 10','count({<PACKAGE_CODE={"2*"}>} distinct(NO_RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"}>} distinct(NO_RULE_11_COUNT))*2','count({<PACKAGE_CODE={"2*"},YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"},YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))*2','if(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)<11,(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)&'.'&CLASSIC_LANE_POINT_NAME))'
'11','By TEU, No Breakdown - Top 20','By TEU, No Breakdown','Top 20','count({<PACKAGE_CODE={"2*"}>} distinct(NO_RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"}>} distinct(NO_RULE_11_COUNT))*2','count({<PACKAGE_CODE={"2*"},YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"},YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))*2','if(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)<21,(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)&'.'&CLASSIC_LANE_POINT_NAME))'
'12','By TEU, No Breakdown - All','By TEU, No Breakdown','All','count({<PACKAGE_CODE={"2*"}>} distinct(NO_RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"}>} distinct(NO_RULE_11_COUNT))*2','count({<PACKAGE_CODE={"2*"},YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))+count({<PACKAGE_CODE={"4*"},YR={"@(=max({<YR>}YR))"}>} distinct(NO_RULE_11_COUNT))*2','(aggr(Rank(@(vConcatExpressions2)), CLASSIC_LANE_POINT_NAME)&'.'&CLASSIC_LANE_POINT_NAME)'

](delimiter is ',');

LOAD Concat(TYPE_COUNT, ',', ID) As ConcatExpression ,
Replace(Concat(TYPE_SORT, ',', ID), '@', '$') As ConcatExpression2,
Replace(Concat(TOP_FILTER,',',ID),'@','$') as ConcatExpression3

Resident Table_merged;

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

 

0 Replies