Alternative to load distinct

[#1]

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

[johnw · Accepted solution]

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.

[nagaiank]

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.

[johnw]

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".

[nagaiank]

Thank you.

[#6]

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

[johnw]

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.

[#8]

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

[johnw]

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. 

[farolito20]

How is with 3 columns?