Mapping query

mb1824

Hi,

I have a list of divisions [01 to 17]

How do I write a mapping condition that applies to all but "08"?

I would prefer not to define the upper limit in case "18" appears down the track.

I need something like

• NOTIN "08" or
• 01>07 AND 09+

TIA

JohnGoodwin

Besides some of the methods that have already been provided there is also the option of a single SQL mapping to handle it.

Example based on entity but it would be the same concept for any dimension.

Cheers

John

Matthias Heilos

Hi,

Here are two ways to do this:

1) Create two BETWEEN Mappings: one for 01 to 07 and one for 09 to the upper limit that works. If there aren't any other mappings for other division, you could set it to 99

2) If you want to map 08 to a specific value, you could create one EXPLICIT mapping which would take care of your exceptions for the 08 division and then you could create a LIKE * mapping to take care of anything else.

Hope this helps, otherwise please provide some more detail.

Regards,

Matthias

JohnGoodwin

Besides some of the methods that have already been provided there is also the option of a single SQL mapping to handle it.

Example based on entity but it would be the same concept for any dimension.

Cheers

John

amith.madisetty

Hi John,

Is sql query mean something like case when  etc...  But how do we denote source mapping and assign to target mapping etc??  Are there any keywords to refer source and target??

Thanks

Amith

JohnGoodwin

The source and target are referred to in the example CASE statement, it is basically saying if the entity source value is '08' then the target value will be 'Map 08'

If you want to know know more have a look at Conditional Mapping using a Mapping Script

Robert Angel

As a sql demon loving that feature!!!

amith.madisetty

Ah..  Thanks John got it now.

mb1824

Hi @JohnGoodwin

Thank you for the suggestion. I got one example of this to work, but I am having trouble with my next requirement (syntax below). When I don't include this rule, every account maps as intended. When I include this rule, a heap of accounts are left un-mapped. I can't find any logic as to why some are still mapped using existing rules and others are not. Any ideas?

Robert Angel

-- add this between your last when and the end

else 'Change This for all other codes'

or can be

else ACCOUNT  (if your default is to do nothing but use the straight account)

or add as many 'When' clauses as it takes to cater for all your accounts....

JohnGoodwin

The column will not be "DEPARTMENT", you need to check the "Data Table Column Name" in target application dimension details, it is possibly "ENTITY" or one of the UD columns

mb1824

Thank you @JohnGoodwin, it should be 'UD2', however there is something wrong with the first WHEN statement. It doesn't seem to recognise ....IN '01,02,06'.....as a list of values

The 2nd WHEN statement with ...IN '8'... works correctly