Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This data is set up so that there is 1 of three values selected per review, per question. Originally I tried to take care of this in expressions but was having trouble...so I created a field for each value int he transformation like so and even went back and explicitly made these fields numeric:
QRFieldValue:
NoConcatenate
Load
[FieldValueId]
,[ValueName]
,num(if(ValueName = 'OK', 1, 0)) as OKCount
,num(if(ValueName = 'N/A',1, 0)) as NACount
,num(if(ValueName = 'NI',1, 0)) as NICount
// ,InsertDate
From [$(vSourceQVD)$(vSourceQRA)QRFieldValue.qvd](qvd);
Store QRFieldValue into [$(vTransformQVD)$(vSourceQRA)QRFieldValue.qvd](qvd);
Drop Table QRFieldValue;
see below: the top table shows the data at a review level and you can see the appropriate field has appropriate value given the value name.....but even though this is a numeric field and I am using a sum Aggregate, it seems to be treating it as Boolean (See Totals). It does this anywhere I try to aggregate these fields....
Has anyone seen this?
Outside of creating a separate aggregate data set in the transformation, is there a way I can get it to treat these values as numbers and aggregate them appropriately?
