Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
I'll give it a shot, this weekend.
Kind Regards