Store dimension/table into a variable and looping to create new dimension
Hello,
I have several questions. Suppose I have a table like this:
id
price
1
10
2
20
2
30
4
40
5
50
[table]:
LOAD
id,
price
FROM...
1. Is it possible to store a dimension into a variable? Like in other programming languages, there is a list datatype. For example, in Python it would be:
id = table["id"]
print(id)
Output: [1, 2, 3, 4, 5]
2. If the answer is yes, is it possible to store that variable into a table? In Python:
table["quantity] = [4, 7, 3, 20, 5] // In table named "table", creates a "quantity" dimension with values 4, 7, 3, 20, 5
Result:
id
price
quantity
1
10
4
2
20
7
3
30
3
4
40
20
5
50
5
3. Is it possible to loop through every value in a dimension?
In Python to create a new dimension, which tests if the price is lower than 30:
isPriceLowerThan30 = []
For value in table["price"]:
if value < 30:
isPriceLowerThan30.append("True")
else:
isPriceLowerThan30.append("False")
This returns a list isPriceLowerThan30 = [True, True, False, False, False]
table["isPriceLowerThan30"] = isPriceLowerThan30 // Creates a new dimension "isPriceLowerThan30" in table named "table"
Result:
id
price
isPriceLowerThan30
1
10
True
2
20
True
3
30
False
4
40
False
5
50
False
3. What is the difference between '' or ' ' (empty string) and <null>? How to check if a value is an empty string or null?
1) & 2) Variables are value containers of type dual(), a special QlikView type that holds either a numerical value, or a text representation or both. But nothing more. There is no list type in QlikView Script, unless you serialize your list into a string and store it as a text-only value in a variable. That should answer the first two questions.
QlikView script is nothing like Python but more like VBScript (if you want to make a bad comparison)
First 3) You can attach new "columns" (actually fields) to a table by transferring the data into a new table and calculating the correct values for the new column on the fly.
Second 3) '' is an empty string, ' ' is not an empty string (just whitespace) and Null is a value that indicates that there is just nothing (cf. in database terminology, NULL means uninitialised = not even empty or whitespace)
Thank you for your answer. Could you give me some syntax examples of how should it be done? I imagine it be should something like this:
1. Declare an empty string variable
2. Loop through every value in a dimension which you want to test/modify
3. Append the result to the string variable with a separator (example: ,)
Result would be:
a = 'true,true,false,false,false'
4. Then assign this variable to a new dimension (how?). I imagine it would be needed to split the string based on the separator. Create an empty string variable, loop through every letter of the a variable, and append each letter until ',' separator, then create a new one and do it again.
A syntax example would be very nice, since I understand from syntax example very well.
Dimensions are created from data in fields. The fact that it doesn't lend itself to aggregation makes a filed into a dimension, whether you eventually put it on a sheet or not.
When you load the first table (from an external source I assume), you will probably have two fields at your disposal. In QlikView, the most efficient method to create deduced information is to do that while loading the original data anyway. And not by looping at a second stage all over again. For example, if your data comes from a QVD (the typical external data storage container in QlikView land), you could do it like this:
MySimpleTable:
LOAD id,
price,
IF (price < 30, true(), false()) AS PriceIsLowerThan30
FROM MySimpleSourceFile.QVD (qvd);
If you now put a ListBox that shows field PriceIsLowerThan30 on a Sheet, it will only display two distinct values. Selecting one of those values will cause the associative database to deactivate all values that have been tagged with the other value.
Two further remarks:
QlikView Script is a language that is extremely optimised for fast data processing. It is not a general purpose language, more like a domain specific language.
This means that whatever you perform in the context of a LOAD statement cannot be easily replaced by another construct to get better performance. If at all...
