My data has a number of vectors and I want to compute a linear combination of them. Each vector has an ID and different values for each year, so my aggregation into a new ID (99) is:
Data:
Concatenate
LOAD
99 as ID,
Year,
Sum(Value*Factor) as Value
Resident Data
Group By Year;
I want the user to be able to set the factor for each ID so I created a Control table with fields (ID, UserFactor), making the UserFactor field of type InputField so it can be edited in the UI.
To get the factors I put the ID into variable vID and use
Lookup('UserFactor','ID',$(vID),'Control')
This works fine if field UserFactor is NOT an InputField, but fails if it is.
1. Is this a restriction for InputField fields?
2. Is there a better way to allow users to enter data to be used in table processing?
Rather than mess about with many variables I found a neat solution.
To recap, I have a table of (key,value) pairs displayed in the UI where the user types in the values and then a Partial Reload is used to process the data using these values. But the "value" is an InputField and this does not survive a Partial Reload if it is referenced in the script.
My workaround is to create a temporary table in the script, but instead of generating it directly from the parent table, the contents are saved in two variables which are then used in the LOAD statement.
The steps are as follows:
1. User enters values into the MyValue fields for table Control(MyKey,MyValue) and then selects a button labeled "Do It"
2. The button has Actions:
-- Set Variable vKeys=concat(MyKey,',',MyKey)
-- Set Variable vValues=concat(MyValue,',',MyKey)
-- Reload (Partial)
The third parameter in the concat() calls ensures that the two lists are in the same order.
3. The script creates a temporary table from the variables:
tmpControl:
Replace LOAD
SubField('$(vKeys)',',')as tmpKey,
SubField('$(vValues)',',',RowNo()) as tmpValue
AutoGenerate 1;
The first SubField function (with two parameters) creates one record for each Key, but the second SubField Function (with three parameters) provides just one value for each record.
4. The remainder of the script uses this temporary table instead of the original, which is now preserved in the UI and can be re-used at will.
I often find that the "obvious" implementation does not work in QlikView, but on this occasion there is an elegant (maybe!) solution, if a little obscure.
InputField has a lot of special restrictions that can make them a pain to use. I find using variables quite a bit more predictable and reliable. But the downside is that you have to do a bit more handling yourself of the creation and deletion of variables and it depends on the number of data values that you need to let the users modify.
How is the usage in your scenario? How much data need to be modified - a handful - some tens of data - hundreds or in the thousands?
When it comes to InputField you also need to use special "Input Field Aggregation Functions" the normal ones will not do.
Don't believe this if you look in the QlikView Reference Manual:
An input field is just like any other field in QlikView, with the difference that its values, as read in the
script, can later be changed without running the script again. Input fields can be used like any other fields in
all types of sheet objects.
It is simply not true .... Input Fields ARE NOT as any other field in QlikView... I literally wasted several days in a project believing this before I just had to give up. Be prepared that they work in a weird manner with a lot of restrictions and suddenly something like a JOIN or CONCATENATE might break them...
In fact my problem is even stranger - the InputField Lookup works in a LOAD statement but not in an ADD LOAD statement. But I need to use Partial Reloads to let the user perform a variety of operations.
I'll try using variables, but it's going to be messy if I have an arbitrary number of them!
Yes that is true ... so you need to code some helper VBscript macros to administer these variables... and you can "emulate" arrays by adding an index number to each variable name. That way you can use $-sign expansion to reference the "array" values by index number or even by "names" if you like...
Rather than mess about with many variables I found a neat solution.
To recap, I have a table of (key,value) pairs displayed in the UI where the user types in the values and then a Partial Reload is used to process the data using these values. But the "value" is an InputField and this does not survive a Partial Reload if it is referenced in the script.
My workaround is to create a temporary table in the script, but instead of generating it directly from the parent table, the contents are saved in two variables which are then used in the LOAD statement.
The steps are as follows:
1. User enters values into the MyValue fields for table Control(MyKey,MyValue) and then selects a button labeled "Do It"
2. The button has Actions:
-- Set Variable vKeys=concat(MyKey,',',MyKey)
-- Set Variable vValues=concat(MyValue,',',MyKey)
-- Reload (Partial)
The third parameter in the concat() calls ensures that the two lists are in the same order.
3. The script creates a temporary table from the variables:
tmpControl:
Replace LOAD
SubField('$(vKeys)',',')as tmpKey,
SubField('$(vValues)',',',RowNo()) as tmpValue
AutoGenerate 1;
The first SubField function (with two parameters) creates one record for each Key, but the second SubField Function (with three parameters) provides just one value for each record.
4. The remainder of the script uses this temporary table instead of the original, which is now preserved in the UI and can be re-used at will.
I often find that the "obvious" implementation does not work in QlikView, but on this occasion there is an elegant (maybe!) solution, if a little obscure.
