topic Re: Normalize anomalies in data in LOAD in App Development

Normalize anomalies in data in LOAD

shearwatertroy — Mon, 20 Jun 2016 18:20:09 GMT

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.

Re: Normalize anomalies in data in LOAD

swuehl — Mon, 20 Jun 2016 19:26:16 GMT

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 ...;

Re: Normalize anomalies in data in LOAD

Anonymous — Mon, 20 Jun 2016 19:26:50 GMT

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

Re: Normalize anomalies in data in LOAD

shearwatertroy — Mon, 20 Jun 2016 21:05:49 GMT

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";

Re: Normalize anomalies in data in LOAD

marcus_sommer — Tue, 21 Jun 2016 08:33:47 GMT

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

Re: Normalize anomalies in data in LOAD

shearwatertroy — Tue, 21 Jun 2016 14:42:30 GMT

That fixed it thank you guys so very much!