Andrew, you can do it using dual() function. No need for an additional field. Can be done with mapping or with 4-level "if".
MAPPING LOAD * INLINE[
Increased A lot, 2
Increased a little, 1
Not changed, 0
Decreased a little, -1
Decreased a lot, -2
dual([How has], applymap('MyMap',[How has])) as [How has],
The new [How has] is the text and the number at the same time.
Technically it is fine, but not good from the data modeling point - you create additional table which is unnecessary. Dual is better.
See example attached. notice taht I have two lisy boxes for Answer - one is in text fromat, another in numeric, and it is the same field.
Dual.qvw 142.0 K
I missed the point that my solution only works if you have the numeric values in the transaction table (not the text) and you want to introduce a dual text value. So in this case it will not work, the applymap solution is one way to go.
I think you misunderstood the solution I presented. Yes I introduce a table in the model, but it is only temporary. I delete it after population my field with transaction data. The applymap-method is much slower and breaks the optimized load of qvds, the predefined dual method is faster and does not break optimized load.
MyTempDualMaker: LOAD dual(A,B) as Answer INLINE [ A, B Increased A lot, 2 Increased a little, 1 Not changed, 0 Decreased a little, -1 Decreased a lot, -2]; YourDataSource: LOAD * INLINE [ Name, Answer Arnold, 2 Jenny, 1 Max, 0 Heather, -1 Vegar, 0 Ron, 2 ]; DROP TABLE MyTempDualMaker;
thanks for all your help, but really not getting on very well with this after scouring through everything youve said.
I've added this to the load script:
LOAD dual(A,B) as [UncertaintyComparedToLastYear] INLINE [
Increased significantly, 2
Increased somewhat, 1
Broadly unchanged, 0
Declined somewhat, -1
Declined significantly, -2
<<<Then I carry on loading my data.... there are several tables>>>
Year (Created) as Year,
It just doesnt seem to be working... can you help?
the uncertainty field is the one I want to have mapped - you can see the 5 possilbe options from the MyMap
Kind regards and thanks for your help,