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.
