Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

shearwatertroy
New 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

Re: Normalize anomalies in data in LOAD

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

5 Replies
MVP
MVP

Re: Normalize anomalies in data in LOAD

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

bellamae
Valued Contributor

Re: Normalize anomalies in data in LOAD

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

shearwatertroy
New Contributor III

Re: Normalize anomalies in data in LOAD

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

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

Re: Normalize anomalies in data in LOAD

That fixed it thank you guys so very much!