I'm trying to work-around, but work with ( I think? ), Qlikview's associating logic. It's hard to explain without an example, so here's one..
I have some data: Some dates, Some Pkey, and Some Account #'s
Below data(DATE FIELD) is in a ListBox: Below Data is in a TableBox:
DateDue: Pkey MonthCreated AcctNum DateDue 11/3/2016 1234 Oct 4567 11/3/2016
11/4/2016 2345 Oct 2345 11/4/2016
11/5/2016 3456 Oct 2589 11/5/2016
11/6/2016 4567 Oct 7845 11/6/2016
My user has a list box (on the left) that they can select a date (DateDue), When they make a selection, my TableBox (Right) shows a list of all of my AcctNum's with a due date of that date (usually that day or the next day). This is great, it is easy to find Account Numbers by their due date to prioritize. The problem, however, is I would like my user to know when there are more Accounts due on a different date than that particular due date, and have a different Pkey, but the same AcctNum. Ideally, when the user selects the DateDue, they would have a list of all of the accounts that are DUE on that Date, PLUS a list of accounts that correspond to the SAME Account Number.
I'm sure this is possible with qlikview, I just don't know how to stop QV from associating those DateDue's and Associate the AcctNum...
John - I know the two expressions are identical with regards to the result. Do you know if the performance wise they are the same also? The reason I ask is this is because that I have been thinking that search string almost defeats the purpose of doing a chart level calculations (because it seems to check almost row by row). Does it mean that it might be slower? I have no idea and I have not tested this, but I was wondering what might you have to say about this?
My expectation is that the performance will be identical. My expression between double quotes I would expect to search only on the internal list of possible Date values, which is small, perhaps hundreds to thousands in typical data models, taking a tiny fraction of a second even if it's exhaustively going down the whole list. At that point, it has a list of specific dates, "selects" those, and the associative data model should take care of the rest.
And I would guess that your expression between quotes would do the same - it isn't already an explicit list of values that can be selected, so QlikView would do the equivalent of what it would do with a search expression, which I expect is going down the list of hundreds or thousands of dates and "selecting" those that match.
My expectation is that the performance will be identical. My expression between double quotes I would expect to search only on the internal list of possible Date values, which is small, perhaps hundreds to thousands in typical data models, taking a tiny fraction of a second even if it's exhaustively going down the whole list. At that point, it has a list of specific dates, "selects" those, and the associative data model should take care of the rest.
Would this still be true if you have say 50 million or 500 million records and date sits in the fact table and there is no master calendar table? Just trying to understand If the search string is actually doing it row by row or is this doing this for distinct dates.
Hopefully this is a fair comparison. Generated 50 million rows of data. I initially had a master calendar, then removed it to remove any concern that the master calendar was giving the expression a shortcut. Saved the app on the Main tab. Closed QlikView and app to presumably clear my cache. Reopened QlikView and app. Switched to Sheet 1, which has the set analysis one way. Checked calculation time of the chart. Closed Qlikview and app to presumably clear my cache. Reopnened QlikView and app. Switched to Sheet 2, which has the set analysis the other way. Checked calculation time of the chart. Repeat ten times. Here are the set analysis expressions and the calc times.
I don't understand how, and I kind of don't believe it, but it seems like the first expression is sometimes faster. The other isn't your expression, but is just a slightly different take on where to do the dollar sign expansions.
I used the attached, but loaded it here with only 100 rows so that it wasn't hundred of megabytes.
This looks like a nice test, I will do the same at my end and will come back with what I found. Thanks for the sample John, I plan to use the same sample as the one you have provided.
