Dynamic Classification of Cross-Dimensions

Wed, 04 Nov 2009 18:17:04 GMT

A small into into the problem.

We have a bunch off data with several classifications, ShowTypes, PriceClasses, ReductionTypes, ...

Now we need a system that we can easily group some of these codes in a new classification, ex ShowType1 + (PriceClass 1 & 2) + All ReductionTypes get Class A, ShowType 2 + All PriceClasses + ReductioType Junioir get Class B .....

We could do this on the database level un load it into QV, but our load start from over 800milj records. And it stays quite 'fixed', only after a complete re-upload it is workable.

Is there anyone that has a suggestion on how i could do a kind of this in QV ?

Would greatelly improve the flexibility ...

Thx. Harry.

Dynamic Classification of Cross-Dimensions

johnw — Thu, 05 Nov 2009 03:38:11 GMT

How dynamic does it need to be? Are you trying to make these changes without doing a reload? In that case, I think you'd be stuck with calculated dimension:

if(ShowType=1 and match(PriceClass,1,2),'A'
,if(Showtype=2 and ReductionType='Junior','B'
,... ))

If you can afford to only change it on a reload, you could use the same expression in your main load. You wouldn't have to go back to the database if you're loading from a QVD:

,if(ShowType=1 and match(PriceClass,1,2),'A'
,if(Showtype=2 and ReductionType='Junior','B'
,... )) as NewClass

Main problem there is probably performance running that IF against a billion records and turning an optimized QVD load into an unoptimized load.

There might be a better way, but I'm guessing you could solve the performance problems by building a classification table after the main optimized QVD load (assuming that there are a limited number of each type and class so that this doesn't explode):

[NewClasses]:
LOAD fieldvalue('ShowType',iterno()) as "ShowType"
AUTOGENERATE 1
WHILE len(fieldvalue('ShowType',iterno()))
;
LEFT JOIN
LOAD fieldvalue('PriceClass',iterno()) as "PriceClass"
AUTOGENERATE 1
WHILE len(fieldvalue('PriceClass',iterno()))
;
LEFT JOIN
LOAD fieldvalue('ReductionType',iterno()) as "ReductionType"
AUTOGENERATE 1
WHILE len(fieldvalue('ReductionType',iterno()))
;
LEFT JOIN
LOAD *
,if(ShowType=1 and match(PriceClass,1,2),'A'
,if(Showtype=2 and ReductionType='Junior','B'
,... )) as NewClass
RESIDENT [NewClasses]
;

You could stop there and it should work fine. You'll have a synthetic key, but I think that's fine in this case. It might well calculate the charts a little slower than if NewClass was on the main table. So you might wish to put the NewClass field on the main table instead:

LEFT JOIN ([MainTable])
LOAD *
RESIDENT [NewClasses]
;
DROP TABLE [NewClasses]
;

If there are too many classification fields or too many values, there's probably an efficient way to extract the actual combinations rather than all possible combinations, such as loading a field that concatenates all the classes during the main load, then subfielding the fieldvalue of it to get your rows. Not sure.

Dynamic Classification of Cross-Dimensions

Thu, 05 Nov 2009 15:32:05 GMT

I'll give it a shot, this weekend.

Kind Regards

topic Dynamic Classification of Cross-Dimensions in QlikView

Dynamic Classification of Cross-Dimensions

Dynamic Classification of Cross-Dimensions

Dynamic Classification of Cross-Dimensions