Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have yes/no flag fields where the values are 1,0 and QV will correctly map it to Yes,No but QS is setting the 1,0 values to ‘Unknown’. The logic in my script is as such:
YesNo_Map:
Mapping Load * Inline
[
OrigValue,NewValue
0, No
N, No
F, No
1, Yes
Y, Yes
T, Yes
no, No
yes, Yes
];
ApplyMap('YesNo_Map', NOTE, 'Unknown') as [Note]
(Side note: NOTE is oracle datatype varchar2 but stores 1,0,null)
Looks like a #Num#() is needed, like this:
ApplyMap('YesNo_Map', Num#(NOTE), 'Unknown') as [Note
I'm not seeing any problems with this, so I'd suggest you check your underlying data to make sure that the values are being passed as expected. You could also try explicitly converting both the mapping field and the NOTE field to text to make sure there's no data type mismatch issues.
This works:
YesNo_Map:
Mapping Load * Inline
[
OrigValue,NewValue
0, No
N, No
F, No
1, Yes
Y, Yes
T, Yes
no, No
yes, Yes
];
Load Field, NOTE, ApplyMap('YesNo_Map', NOTE, 'Unknown') as [Note];
Load * INLINE [
Field, NOTE
A, 0
B, 1
C, 2
D, Y
E, yes
F, zzz
];
Appreciate the feedback, but still leaves me without answers on what the root cause is. When I load the data straight from my oracle tables and leave the fields as is and create a second set of fields using the ApplyMap, there is nothing to indicate there is an issue. This is the only thing I think of to do when you say "make sure that the values are being passed as expected"... they are, that is, the original fields correctly show 1 and 0 but QS ApplyMap changes all values to 'Unknown'. What do we know about the QV and QS engines and how they perform on maybe different server versions or with maybe varying odbc drivers? I'm just shooting from the hip with some of these thoughts.
I can't tell you what the root cause is because, unfortunately, I can't replicate your problem. The script I posted, which is the same scenario, runs without issues.
I'd suggest looking into data type mismatches (apply text() to everything), leading/trailing spaces (trim() everything), etc. as the most likely culprit, but that's just guessing.
Looks like a #Num#() is needed, like this:
ApplyMap('YesNo_Map', Num#(NOTE), 'Unknown') as [Note