Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Keitaru
Creator
Creator

How to perform Data Cleansing in Qliksense dataload Editor?

Hi there,

This apologies for asking this again, probably had been answered before previously. 

Like to know if there are any tutorials etc on how to perform data cleansing via QlikSense Data Load editor and if there are any self help or even support group/community that I can refer to for this as I'm still fairly new to Qlik.

Example I've been loading post-cleansed data into qlik and just building dashboards (via ui) and linking via the script, the simple stuff while performing cleansing either in python etc outside. However I do want to be able to have Qlik eventually perform ETL/data cleansing for me after I've plugged in my raw data and have it load everything automatically.

Would anyone be able to point me in the right direction? 

Also I do like to post another question I've want to say have QlikSense extract certain data from my data source and only extract data based on certain conditions via the script after loading the data in.

came across some other post stating the use of 

Load <field>

 

Example of my code:


Load Form_Name_01
FROM [lib://AttachedFiles/All Problem.xlsx]
(ooxml, embedded labels, table is "All_Problem")
Where Form_Name_01 = "Problem Investigation"; 

Is there an example where I can only load e.g. all data based on condition from a specific row?

 

 

 

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
jheasley
Luminary Alumni
Luminary Alumni

I do pretty extensive cleansing in Qlik Sense, but it would help to know what kind of data youre dealing with and what type of cleansing you're doing.  

I use 2 basic processes:

1) for simple logic (i.e. identifying bad weights), I use basic IF logic to flag and update the record.  I would end up with 3 fields, [Original],[FLAG],and [New]. You can then use the cleansed value while preserving the original. the flag enables you to review your defects later. 

Load

[Original Weight],

if([Original Weight] >=45000,1,
if([Original] <=100,1,0)) as [FLAG BAD WEIGHT],

if([Original Weight] >=45000,44999,
if([Original Weight] <=100,101,[Original Weight])) as [NEW WEIGHT]

From [SOURCE];

 

2) The second method uses mapping tables.  Assume for a moment that you don't always get product coding on every order.  Load a series of mapping tables to create the cleansed data. Let's say i have a mapping table for each store that provides the coding by product number. 

Load

If(Store=1001,applymap('1001Prod',Product,'Not Found'),
If(Store=1036,applymap('1036Prod',Product,'Not Found'),
If(Store=1058,applymap('1058Prod',Product,'Not Found'),
If(Store=1071,applymap('1071Prod',Product,'Not Found'),'Not Found'))))  as [ProductCode.Final]

From [Source];

 

Hope this helps!

 

 

View solution in original post

1 Reply
jheasley
Luminary Alumni
Luminary Alumni

I do pretty extensive cleansing in Qlik Sense, but it would help to know what kind of data youre dealing with and what type of cleansing you're doing.  

I use 2 basic processes:

1) for simple logic (i.e. identifying bad weights), I use basic IF logic to flag and update the record.  I would end up with 3 fields, [Original],[FLAG],and [New]. You can then use the cleansed value while preserving the original. the flag enables you to review your defects later. 

Load

[Original Weight],

if([Original Weight] >=45000,1,
if([Original] <=100,1,0)) as [FLAG BAD WEIGHT],

if([Original Weight] >=45000,44999,
if([Original Weight] <=100,101,[Original Weight])) as [NEW WEIGHT]

From [SOURCE];

 

2) The second method uses mapping tables.  Assume for a moment that you don't always get product coding on every order.  Load a series of mapping tables to create the cleansed data. Let's say i have a mapping table for each store that provides the coding by product number. 

Load

If(Store=1001,applymap('1001Prod',Product,'Not Found'),
If(Store=1036,applymap('1036Prod',Product,'Not Found'),
If(Store=1058,applymap('1058Prod',Product,'Not Found'),
If(Store=1071,applymap('1071Prod',Product,'Not Found'),'Not Found'))))  as [ProductCode.Final]

From [Source];

 

Hope this helps!