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