Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shearwatertroy
Contributor III
Contributor III

Normalize anomalies in data in LOAD

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.

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

5 Replies
swuehl
MVP
MVP

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
Not applicable

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

shearwatertroy
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

That fixed it thank you guys so very much!