DUAL - Strange behavior

[tschullo]

I have been wrestling with this problem for several hours now, so I broke it sown to as imple script.

The goal was to produce a [Risk Type] column comprised of the Name as the string value and  ID as the numeric value

where the Name is changed to Z whenever the quarter is >= to 2016 Q2. As you can see this is not working.

Note the commented out line which was my first attempt that did not work:

     //if(Year*10+Qtr>=20162,Dual('Z',ID),Dual(Name,ID)) as NewName

So I decided to not do the dual and try on a second pass in RiskRatings.  this still did not work.

I would appreciate any help.

TEST:
Load * INLINE [
Year, Qtr, Name, ID, Key
2016, 1, A, 1, 2016-1-1
2016, 1, B, 2, 2016-1-2
2016, 1, C, 3, 2016-1-3
2016, 1, D, 4, 2016-1-4
2016, 2, A, 1, 2016-2-1
2016, 2, B, 2, 2016-2-2
2016, 2, C, 3, 2016-2-3
2016, 2, D, 4, 2016-2-4
];

Rating:
LOAD
Key,
ID as ID2,
//if(Year*10+Qtr>=20162,Dual('Z',ID),Dual(Name,ID)) as NewName,
if(Year*10+Qtr>=20162,'Z',Name) as NewName2
Resident TEST;

RiskRatings:
Load Key,
Dual(NewName2,ID2) as NewName,
1 as RiskCnt
Resident Rating;

[swuehl · Accepted solution]

Each numeric value can only show a single dual value text representation. It seems you are trying to assing the sam ID number different text representations.

Does this explain your issue?

[tschullo]

Yep, I was just re-reading the DUAL "help" and it states that quite succinctly.

So basically the numeric part of your dual becomes a unique key (if you will) into the string that goes with it.

Since I have some string values that precede 2016Q2 that are different (and older) the text displayed will be the first encountered in the table to match the new DUAL number value.  What I did to get around this is whenever I changed the text, I also changed the ID (2 became 2.5, etc) and that worked.

Thanks,

Tony