Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ];
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;
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
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;
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
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 ];
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