Switch inside Load

[#1]

Hi All,

I know there is no switch statement inside a load, and that a mapping can sometimes fill that void.  In my case, it will not.  I'm hoping someone else has solved a problem that I would normally solve with case in SQL.

I rely on text files produced by a reporting system.  The reports are generated daily.  The agent's phone extension serves as the id for the rows.  The phone extension just changed for one agent, and will be changing for quite a few more.  The existing data will have the old extension - the new data will have the new one.

I have a temporary fix for the one agent: within the load statement I have

If([Agent number]='1358','7216', [Agent number]) as USERDN,  

1358 is the old ID, 7216 is the new one.  A switch statement would allow me to handle more than one ID in this manner.

Since that is not available, I'm open to suggestions.

[swuehl · Accepted solution]

Are you sure a mapping won't fit your needs here?

Maybe like

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1358, 7216

1359, 7217

1400, 8123

];

LOAD

...

applymap('map1', [Agent number]) as USERDN,

...

from ...;

If no matching value is found in the mapping table, the [Agent number] will be used for USERDN.

Hope this helps,

Stefan

[johnw]

The equivalent of a switch is probably pick(match()):

pick(1+match([Agent number],'1358','1234')
             [Agent number],'7216','4321') as USERDN,

However, it would probably be better to use a mapping table, either maintained inline or in an Excel file.

[Agent Map]:
MAPPING LOAD * INLINE [
From, To
1358, 7216
1234, 4321
];

And then in your main load:

applymap('Agent Map',[Agent number]) as USERDN,

Both of these approaches default to the [Agent number] if it isn't found in the pick or map.

Edit: Stefan posted while I was writing and beat me to it. 🙂

[#4]

Hey, thanks!  Both of you got it right!

I wasn't sure how the syntax worked for the mapping table when the column was the same.  Thanks for the quick replies. 

Can the correct answer be shared?  Otherwise, I'll give Stefan the correct and John the Helpful because Stefan types faster

[johnw]

Just one correct.  Stefan wins it for typing faster.