Normalize anomalies in data in LOAD

[shearwatertroy]

Hey everyone,

I'm working with some legacy data that I am not allowed to alter within a DB.

I'm trying to change things as simple as State/Province data entry errors. So I want ON instead of (ONT, Ontario, ONTARIO). Do you know of any way to change these values?

Thanks in advance.

[swuehl]

Have a look at

Data Cleansing

MAP:

MAPPING

LOAD FROM, TO INLINE [

FROM, TO

ONT, ON

Ontario, ON

ONTARIO, ON

];

Table:

LOAD ApplyMap('MAP', State) as State,

          ...

FROM ...;

[Anonymous]

You could use an nested if statement but that can be quite clunky.  You could also try a mapping table.

[shearwatertroy]

That's a great source, and seems like it's the correct path, but unfortunately it didn't work. I waited 15 minutes for my data to load and I eventually had to abort the load.

Here's what I placed it into, please let me know if I have any syntax errors:

MapStates:

MAPPING LOAD

  [Sell-to County], "State"

Inline [

  [Sell-to County], "State"

  ONT, ON

  Ontario, ON

  Newfoundland, NL

  Quebec, QC

];

SalesInvoiceHeader:

LOAD

  "No_" as 'Document',

    "Bill-to Customer No_",

    "Sell-to Customer Name",

    ApplyMap('MapStates', [Sell-to County]) as "State",

    "Sell-to Country_Region Code" as "Country",

    "Order Date",

    "Shipment Date",

    "Due Date",

    "Shortcut Dimension 1 Code",

    "Customer Posting Group",

    "Salesperson Code";

SQL SELECT * FROM "Sales Invoice Header";

[marcus_sommer · Accepted solution]

I think the reason are the square-brackets within the inline-statement which you don't need in your case:

MapStates:

MAPPING LOAD

  [Sell-to County], "State"

Inline [

  Sell-to County, State

  ONT, ON

  Ontario, ON

  Newfoundland, NL

  Quebec, QC

];

then the first line from it will be interpreted as valid fields even if they have spaces or any special-chars.

- Marcus

[shearwatertroy]

That fixed it thank you guys so very much!