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