topic Re: Alternative to load distinct in QlikView

Alternative to load distinct

Tue, 17 May 2011 22:41:16 GMT

Everyone, I'm looking for an alternative to a "load distinct" type syntax used under the following scenario:

Load A,B,C, Date

from fact;

Load distinct Date

from fact;

I know that in this particular case an autogenerated master calendar approach would be better, however I'm just trying to find out a different way of loading only the distinct values in a particular field. Your help is very appreciated!!!

Thanks in advance

Regards

Re: Alternative to load distinct

johnw — Tue, 17 May 2011 22:48:59 GMT

Do it like this:

LOAD date(fieldvalue('Date',iterno())) as Date
AUTOGENERATE 1
WHILE len(fieldvalue('Date',iterno()));

This is VERY efficient. It basically steps through QlikView's internal data to get your values without even referencing your original table. So even if your original table has 100 million rows, if there are only 1000 Dates in it, this load will be over in a flash.

Now, it probably is still a good idea to do load distinct when you have a small table, if only because it is easier for newer developers to read and duplicate. I don't, though. I always use the above approach, regardless of table size, so that my code stays consistent.

Re: Alternative to load distinct

nagaiank — Tue, 17 May 2011 23:15:04 GMT

John,

Thank you for the alternative and efficient way for getting distinct values of a field in a table.

However I have the following question. If Sales fact table has a 'Date' field having those dates on which there are sales data and if the 'Date' field in the [Master Calendar] table has every date from the minimum date to the maximum date, which 'Date' field will be used by the 'fieldvalue' to select the distinct dates?

Regards.

Re: Alternative to load distinct

johnw — Tue, 17 May 2011 23:22:03 GMT

A good question. Fieldvalue() will use every value already loaded in ANY table since it doesn't reference a specific table. One possible way around this in your example is to first load the sales fact table, then do the fieldvalue() load, and only THEN load your master calendar. If that's impractical, another solution is to create a duplicate field, loading "Date" both as itself and as "TempDate", and then do the fieldvalue() load with "TempDate", and then drop "TempDate".

Re: Alternative to load distinct

nagaiank — Tue, 17 May 2011 23:24:38 GMT

Thank you.

Re: Alternative to load distinct

Wed, 18 May 2011 14:27:38 GMT

Thanks John, indeed is very efficient and also is a clever way to avoid the load distinct. I'm going to use it from now on!!!. I played a little bit with the code, just for the fun of it and found QlikView behaving weird from version to version, here is the code snippet:

let vFieldRows = FieldValueCount('Date');

LOAD evaluate(fieldvalue('ID',iterno())) as DistinctDate

autogenerate(1)

while iterno() <= $(vFieldRows);

When I tried it on version 9 SR6 x64 the code works fine and return the correct date number, however when I used version 10SR2 x64 it returns odd numbers, does anybody know why?? is it a bug???

Regards

Re: Alternative to load distinct

johnw — Wed, 18 May 2011 18:50:15 GMT

I don't think evaluate() works here. Date() may be redundant, but I often have problems with QlikView recognizing what type of field something is after using fieldvalue(), so I pretty much always override it. I never thought to use fieldvaluecount(), so that's a nice simplification - thanks! However, using a variable seems unnecessary, and since we have a count, we don't need to iterate; we can just generate that number.

LOAD date(fieldvalue('Date',recno())) as Date
AUTOGENERATE fieldvaluecount('Date');

That works fine for me in v10 SR2 x32.

Re: Alternative to load distinct

Thu, 19 May 2011 20:04:18 GMT

Thanks again John, you are right the loop is completely unnecesary, I missed that part, I don't seem to be thinking clearly lately. I used the evaluate function just to see what happend and it really surprised me because it does not seem to behave the same between version 9 and 10, that's akward.

Regards

Re: Alternative to load distinct

johnw — Thu, 19 May 2011 20:09:49 GMT

I've been using a loop for this for years. If anyone should be embarrassed for not spotting that we could get rid of it, it's me.

Re: Alternative to load distinct

farolito20 — Thu, 03 Jan 2013 20:22:46 GMT

How is with 3 columns?

Re: Alternative to load distinct

johnw — Thu, 03 Jan 2013 20:36:29 GMT

farolito20 wrote:
How is with 3 columns?

So you're asking how to load distinct values of three columns from a source? So you're asking how to do this?

DistinctValues:
LOAD DISTINCT A,B,C
FROM SOMEWHERE
;

I'd probably do it just as shown above. The reason we could speed up finding distinct values of a single column is that QlikView was already tracking the distinct values internally. But QlikView isn't tracking distinct combinations of values internally, so far as I know. Still, for the sake of argument, we can sort of do it with fieldvalue() with some setup. I just expect this would perform worse instead of better, and it's certainly more complicated and more difficult to maintain. So I recommend not doing the following:

// For amusement only

Temp:
LOAD A&':'&B&':'&C as ABC
FROM SOMEWHERE
;
DistinctValues:
LOAD
subfield(ABC,':',1) as A
,subfield(ABC,':',2) as B
,subfield(ABC,':',3) as C
;
LOAD text(fieldvalue('ABC',iterno())) as ABC
AUTOGENERATE fieldvaluecount('ABC')
;
DROP TABLE Temp
;

Re: Alternative to load distinct

farolito20 — Thu, 03 Jan 2013 21:41:26 GMT

not doing?

Re: Alternative to load distinct

johnw — Thu, 03 Jan 2013 21:44:01 GMT

Right. Use LOAD DISTINCT for three columns. The fieldvalue() alternative "would perform worse instead of better, and it's certainly more complicated and more difficult to maintain."