To become really good at QlikView, you have to really understand how QlikView stores data. This is an introduction that should help you along that road.
If I load a very simple dataset:
Fruit:
LOAD *
INLINE [
Fruit, Color
Banana, Yellow
Blueberry, Blue
Tomato, Red
Tomato, Green
];
And then add both fields as list boxes, I see that there are only 3 fruit listed - even though I have loaded 4 entries. "Tomato" is only stored once by QlikView. Internally, there are binary keys keeping track of the association between "Tomato" along with "Red" and "Green".
In a large data set with a lot of repeating values (especially text values) the fact that each field value is only stored once means that there is a huge saving in the amount of memory needed to store the information. This is why QlikView can store so much data in memory.
Let's add another table of data:
Colors:
LOAD * INLINE [
Color, Red, Green, Blue
Yellow, 255, 255, 0
Blue, 0, 0, 255
Red, 255, 0, 0
Green, 0, 255, 0
Amber, 255, 121, 0
];
I now might think that I have 2 tables of data - both of which have a field called "Color". But I don't! There is only one field called "Color" in our dataset and this field will have 5 values. Internally, the association between each of them and their associated values are maintained with binary keys. It just so happens that one of the values - "Amber" - is not associated with any of the values in the "Fruit" field.
Note that there is probably no direct association between "Fruit" and "Red". The association is indirect. However, because of the smaller data set in memory and the binary keys, the association is very fast.
Hope this is useful!
Friday, 16 April 2010
Thursday, 8 April 2010
Radish Bits
A long-time news group buddy of mine, Walter Shpuntoff, has his own blog that covers QlikView. Well worth the read:
http://radishbits.wordpress.com
http://radishbits.wordpress.com
Watch your Security Connections
QlikView security is quite simple to implement - you just need to link the security information to the data and you can enable automatic reduction of the information that a user sees. However, you need to watch out for the data that is linking the user to the data - if it is a text field then all of the values in that text field must be uppercase (you know that all the field names in Section Access must be uppercase too, right?)
For example, this straightforward load will enable Tom, Jane or Bill to login:
Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, JANE
USER, BILL, BILL
];
Section Application;
LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
JANE, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
JANE, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
JANE, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];
If you enable the "Initial Data Reduction Based On Section Access" in the "Opening" tab of the Document Properties then they will be restricted to their own data.
However, if I make a slight change:
Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, Jane
USER, BILL, BILL
];
Section Application;
LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
Jane, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
Jane, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
Jane, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];
Now, Jane will not be able to login to this document - because the LNK field has mixed case!
Best advice is to use an integer where possible:
Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, 1
USER, JANE, 2
USER, BILL, 3
];
Section Application;
LOAD * INLINE [
LNK, Name, Month, Sales
1, Tom, Jan, 12321
2, Jane, Jan, 31232
3, Bill, Jan, 32131
1, Tom, Feb, 34342
2, Jane, Feb, 34545
3, Bill, Feb, 76575
1, Tom, Mar, 32133
2, Jane, Mar, 12123
3, Bill, Mar, 21321
];
If you need to link on a text field, make sure that you have all capitals - use the UPPER function if necessary.
For example, this straightforward load will enable Tom, Jane or Bill to login:
Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, JANE
USER, BILL, BILL
];
Section Application;
LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
JANE, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
JANE, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
JANE, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];
If you enable the "Initial Data Reduction Based On Section Access" in the "Opening" tab of the Document Properties then they will be restricted to their own data.
However, if I make a slight change:
Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, Jane
USER, BILL, BILL
];
Section Application;
LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
Jane, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
Jane, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
Jane, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];
Now, Jane will not be able to login to this document - because the LNK field has mixed case!
Best advice is to use an integer where possible:
Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, 1
USER, JANE, 2
USER, BILL, 3
];
Section Application;
LOAD * INLINE [
LNK, Name, Month, Sales
1, Tom, Jan, 12321
2, Jane, Jan, 31232
3, Bill, Jan, 32131
1, Tom, Feb, 34342
2, Jane, Feb, 34545
3, Bill, Feb, 76575
1, Tom, Mar, 32133
2, Jane, Mar, 12123
3, Bill, Mar, 21321
];
If you need to link on a text field, make sure that you have all capitals - use the UPPER function if necessary.
Monday, 22 February 2010
Seeing the Blindingly Obvious
Recently on QlikCommunity, I have proposed a solution however the very brilliant Oleg Troyansky proposed an even better solution: http://community.qlikview.com/forums/p/25381/96915.aspx#96915
Hi answer to the problem (excluding nulls, zero length strings, etc.) was this:
sum({$ < TextField = {"=len(trim(TextField))>0"}>} Qty )
Brilliantly fantastic! Of course, it is even more brilliant if you understand it and, alas, I just couldn't fathom it. It works. It works great. But I didn't see it.
Then my genius colleague, Karol Dorniak, spelt it out for me :- anything that you can type into a search dialog in QlikView can be used in a set! And that includes any "advanced" search syntax!!!
So this is what Oleg was doing - using advanced search. Brilliant.
When you right-click on a List Box in QlikView, there are 3 search options available - Search, Fuzzy Search and Advanced Search. When you click on a list box and just start typing, you get the default search for that List Box (usually the normal search but you can set it to Fuzzy as default on the General tab of the List Box properties). Did you know that you can delete the "**" from the default search and change it to a "~" to start doing a fuzzy search. You can also start typing an "=" followed by an expression (e.g. "=Sum(SalesAmount) > 5000") to do an advanced search - you don't need to go through the advanced search dialog.
So, you can use any of the 3 standard searches by just replacing the search condition with something else ("**", ">", "><", ">=", "~", "=...") Anything that you can type into a search can be used inside a Set!
Man, does this QlikView rock!
Hi answer to the problem (excluding nulls, zero length strings, etc.) was this:
sum({$ < TextField = {"=len(trim(TextField))>0"}>} Qty )
Brilliantly fantastic! Of course, it is even more brilliant if you understand it and, alas, I just couldn't fathom it. It works. It works great. But I didn't see it.
Then my genius colleague, Karol Dorniak, spelt it out for me :- anything that you can type into a search dialog in QlikView can be used in a set! And that includes any "advanced" search syntax!!!
So this is what Oleg was doing - using advanced search. Brilliant.
When you right-click on a List Box in QlikView, there are 3 search options available - Search, Fuzzy Search and Advanced Search. When you click on a list box and just start typing, you get the default search for that List Box (usually the normal search but you can set it to Fuzzy as default on the General tab of the List Box properties). Did you know that you can delete the "**" from the default search and change it to a "~" to start doing a fuzzy search. You can also start typing an "=" followed by an expression (e.g. "=Sum(SalesAmount) > 5000") to do an advanced search - you don't need to go through the advanced search dialog.
So, you can use any of the 3 standard searches by just replacing the search condition with something else ("**", ">", "><", ">=", "~", "=...") Anything that you can type into a search can be used inside a Set!
Man, does this QlikView rock!
Monday, 25 January 2010
More on Dual - Problem in Sets
I have discussed using Dual a couple of times in the past. It can be enormously useful. Sometimes it can throw you a bit - especially if you do not realise that you are using a Dual.
An example comes up in my response to the post of jhoffmann on QlikCommunity.
He was asking about doing calculations in a Set. This is, of course, eminently achievable using the dollar-expansion syntax. You do have to watch it when using a Dual value.
He was using a field called "RollingMonth". I assume, because it comes from the (old) training manual, that this was created in the Script using:
...
Date(MonthStart(date_field), 'MM/YY') As RollingMonth,
...
This syntax creates a Dual value - with the month's date (as an integer) and the text as per the format string.
This is quite fine in a Set if you use a simple calculation such as Max:
Sum({<RollingMonth = {"$(=Max(RollingMonth))"}>} Amount)
You see, Max knows about Dual and will return the correct value so that the comparison will work - it returns the Text value. It is the text value that is required in the Set comparison. If the max month is January 2010, Max will correctly return (in this case) "01/10" - even though it is alphabetically less than the "12/09" before it. Max does the Dual sort on the number and returns the text. Simple.
The problem arises with some other functions that are not so "Dual aware". For example, if we wanted to see the values from the same month last year, we might try this:
Sum({<RollingMonth = {"$(=AddMonths(Max(RollingMonth),-12))"}>} Amount)
The problem is that AddMonths takes a date value as a parameter and returns a date. It doesn't care about the text value. It uses the Dual's numeric value to do the calculation and then returns a date/numeric value. For example, it might be "01/01/2009". This will not work in our Set above (RollingMonth={"01/01/2009"}) because the text doesn't match and the Sum will return 0.
To fix this one, we just need to re-establish the Dual value:
Sum({<RollingMonth = {"$(=Date(AddMonths(Max(RollingMonth),-12),'MM/YY'))"}>} Amount)
Now the calculations will work perfectly.
An example comes up in my response to the post of jhoffmann on QlikCommunity.
He was asking about doing calculations in a Set. This is, of course, eminently achievable using the dollar-expansion syntax. You do have to watch it when using a Dual value.
He was using a field called "RollingMonth". I assume, because it comes from the (old) training manual, that this was created in the Script using:
...
Date(MonthStart(date_field), 'MM/YY') As RollingMonth,
...
This syntax creates a Dual value - with the month's date (as an integer) and the text as per the format string.
This is quite fine in a Set if you use a simple calculation such as Max:
Sum({<RollingMonth = {"$(=Max(RollingMonth))"}>} Amount)
You see, Max knows about Dual and will return the correct value so that the comparison will work - it returns the Text value. It is the text value that is required in the Set comparison. If the max month is January 2010, Max will correctly return (in this case) "01/10" - even though it is alphabetically less than the "12/09" before it. Max does the Dual sort on the number and returns the text. Simple.
The problem arises with some other functions that are not so "Dual aware". For example, if we wanted to see the values from the same month last year, we might try this:
Sum({<RollingMonth = {"$(=AddMonths(Max(RollingMonth),-12))"}>} Amount)
The problem is that AddMonths takes a date value as a parameter and returns a date. It doesn't care about the text value. It uses the Dual's numeric value to do the calculation and then returns a date/numeric value. For example, it might be "01/01/2009". This will not work in our Set above (RollingMonth={"01/01/2009"}) because the text doesn't match and the Sum will return 0.
To fix this one, we just need to re-establish the Dual value:
Sum({<RollingMonth = {"$(=Date(AddMonths(Max(RollingMonth),-12),'MM/YY'))"}>} Amount)
Now the calculations will work perfectly.
More on "Sets =- or -=???"
A while ago I blogged about using =- or -= in a set.
I answered a post in QlikCommunity today (http://community.qlikview.com/forums/p/24894/95176.aspx#95176) that demonstrates the use very well.
The question was from sebagiar. He asked how to get an average out of values while ignoring 0s. For example if I have 3 values: 100, 0, 200 then he wants a result of 150, not the 100 that the straight Avg function will give.
My response is to use the following set on the field:
=Avg({< F1 -= {0}>} F1)
So, why "-=" instead of "=-"? Well, the later just tells the field to use all values except 0 - it will exclude any selections on that field. The first one is "additive" (well, I guess I should say "subtractive") and will include and selections on that field and then drop out the zeros.
I answered a post in QlikCommunity today (http://community.qlikview.com/forums/p/24894/95176.aspx#95176) that demonstrates the use very well.
The question was from sebagiar. He asked how to get an average out of values while ignoring 0s. For example if I have 3 values: 100, 0, 200 then he wants a result of 150, not the 100 that the straight Avg function will give.
My response is to use the following set on the field:
=Avg({< F1 -= {0}>} F1)
So, why "-=" instead of "=-"? Well, the later just tells the field to use all values except 0 - it will exclude any selections on that field. The first one is "additive" (well, I guess I should say "subtractive") and will include and selections on that field and then drop out the zeros.
Tuesday, 22 December 2009
The right negative
I recently had a situation where a customer was not happy with the values returned in a budget variance column.
Simple calculation for variance - (Spend - Budget)/Budget
However, in some of their profit centers there was no budget but there was spend! I.e.: (Spend)/0.
Rather than just display a "-" or 0, they wanted this to display as -100%, to really flag it up.
Following on from my previous post about the RangeMax function in QlikView, I thought about it but then, deciding that wouldn't work for me here, I decided on the Alt function.
The Alt function is quite useful because, like RangeMax, you give it a series of Expressions but what it does is returns you the first one that results in a valid number. So, if I use:
Alt((Spend-Budget)/Budget, -1)
When the variance percentage is Null (when Budget is 0), then the value returned will be -1 (-100%). Exactly what I needed.
Alt can also be useful where you have mixed date formats in a date stream - using multiple Date# functions in an Alt and one of them should give you a number.
Simple calculation for variance - (Spend - Budget)/Budget
However, in some of their profit centers there was no budget but there was spend! I.e.: (Spend)/0.
Rather than just display a "-" or 0, they wanted this to display as -100%, to really flag it up.
Following on from my previous post about the RangeMax function in QlikView, I thought about it but then, deciding that wouldn't work for me here, I decided on the Alt function.
The Alt function is quite useful because, like RangeMax, you give it a series of Expressions but what it does is returns you the first one that results in a valid number. So, if I use:
Alt((Spend-Budget)/Budget, -1)
When the variance percentage is Null (when Budget is 0), then the value returned will be -1 (-100%). Exactly what I needed.
Alt can also be useful where you have mixed date formats in a date stream - using multiple Date# functions in an Alt and one of them should give you a number.
Subscribe to:
Posts (Atom)