Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
srihitha
Contributor III
Contributor III

variable not resolving properly

Hi , 

Can anyone pls help me with below code

I am getting error wen running below script.

There is some issue when i try to use these in load statement - $(vSelectMeasure),$(=vSelectMeasure)

I am trying to fetch minimum string of sales i,e; 150 by resolving the defined variable. 

Let vSelectMeasure ='=MinString(Sales)';

MyTable1: 
Load *,'$(vSelectMeasure)',$(vSelectMeasure),$(=vSelectMeasure);
Load * inline [
Dim, Sales
A, 150
A, 200
B, 240
B, 230
C, 410
C, 330 ];

Labels (4)
2 Solutions

Accepted Solutions
Lisa_P
Employee
Employee

Not sure exactly what you are trying to do, but this may be what you want:

Let vSelectMeasure ='Min(Sales)';

MyTable1:
Load * inline [
Dim, Sales
A, 150
A, 200
B, 240
B, 230
C, 410
C, 330 ];

MinTable:
Load Dim,
Min(Sales) as MinSales
Resident MyTable1
Group by Dim;

View solution in original post

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you look at the script log, you'll be able to see how the vSelectMeasure is substituted in the code. I think then you will see your syntax issue. 

In addition to the syntax issue, you will need to use a  resident load to get the min value from multiple records. Like this:

Let vSelectMeasure ='Min(Sales)';  // Use Min and no = 

MyTable1: 
Load * inline [
Dim, Sales
A, 150
A, 200
B, 240
B, 230
C, 410
C, 330 ];

MinValue:
Load $(vSelectMeasure) as MinValue
Resident MyTable1;

If you want the MinValue to be placed on every row of MyTable1, add a "Join (MyTable1)" to the Minvalue Load statement. 

-Rob

View solution in original post

4 Replies
Lisa_P
Employee
Employee

Not sure exactly what you are trying to do, but this may be what you want:

Let vSelectMeasure ='Min(Sales)';

MyTable1:
Load * inline [
Dim, Sales
A, 150
A, 200
B, 240
B, 230
C, 410
C, 330 ];

MinTable:
Load Dim,
Min(Sales) as MinSales
Resident MyTable1
Group by Dim;

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you look at the script log, you'll be able to see how the vSelectMeasure is substituted in the code. I think then you will see your syntax issue. 

In addition to the syntax issue, you will need to use a  resident load to get the min value from multiple records. Like this:

Let vSelectMeasure ='Min(Sales)';  // Use Min and no = 

MyTable1: 
Load * inline [
Dim, Sales
A, 150
A, 200
B, 240
B, 230
C, 410
C, 330 ];

MinValue:
Load $(vSelectMeasure) as MinValue
Resident MyTable1;

If you want the MinValue to be placed on every row of MyTable1, add a "Join (MyTable1)" to the Minvalue Load statement. 

-Rob

srihitha
Contributor III
Contributor III
Author

Hi Rob ,

If I have to use resident table  to get variable value as you have stated,I dont understand how the below script is working fine. Can you please explain if I am missing some major concept

 

Set vFunction = 'upper'; // Assign the string “upper” to variable vFunction
Set vField = 'String'; // Assign the string "String" to variable vField
Let vEvaluate = '$(vFunction)'&'('&'$(vField)'&')';

// The variable vEvaluate returns the value "upper(string)"

MyTable: // Create table called MyTable
Load *, $(vEvaluate) as Upper; // vEvaluate expanded as a dynamic expression
Load *, '$(vEvaluate)' as Expression; // vEvaluate expanded as string
Load * inline [
ID, String
1, abc
2, def
3, ghi
4, jkl ];

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I was assuming that because you were using MinString(), you want to get the MinString from multiple rows.  Technically you did not need to use a Resident load for that, you could have added a group by in the preceding load. 

-Rob