Forum Stats

  • 3,838,575 Users
  • 2,262,383 Discussions
  • 7,900,689 Comments

Discussions

Mapping query

mb1824
mb1824 Member Posts: 148
edited Mar 13, 2018 7:36AM in Planning and Budgeting

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

Tagged:
Robert Angelamith.madisettymb1824

Best Answer

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Jan 25, 2018 3:37AM Answer ✓

    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.

    pastedImage_0.png

    pastedImage_1.png

    Cheers

    John

    Robert Angelmb1824mb1824
«1

Answers

  • Matthias Heilos
    Matthias Heilos Member Posts: 94
    edited Jan 25, 2018 1:06AM

    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
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Jan 25, 2018 3:37AM Answer ✓

    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.

    pastedImage_0.png

    pastedImage_1.png

    Cheers

    John

    Robert Angelmb1824mb1824
  • amith.madisetty
    amith.madisetty Member Posts: 869 Gold Badge
    edited Jan 26, 2018 8:18AM

    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
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Jan 26, 2018 9:07AM

    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
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Jan 26, 2018 9:17AM

    As a sql demon loving that feature!!!

    amith.madisetty
  • amith.madisetty
    amith.madisetty Member Posts: 869 Gold Badge
    edited Jan 26, 2018 9:51AM

    Ah..  Thanks John got it now.

  • mb1824
    mb1824 Member Posts: 148
    edited Jan 30, 2018 12:14AM

    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?

    Oracle_SQL syntax.JPG

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Jan 30, 2018 2:17AM

    -- 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
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Jan 30, 2018 2:43AM

    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
    mb1824 Member Posts: 148
    edited Jan 30, 2018 6:12PM

    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

This discussion has been closed.