Data Cleansing

[Anonymous]

I wanted to ask what the best way for data cleansing would be in relation to some sale figures and dates.I got an excel files that requires some formatting but I am unsure about the way to proceed to clean the data through the software itself and make it efficient for my data analysis.

[shiveshsingh]

Hi

What type of cleansing do you want?

Handling Cross tables, Nulls, Junks, Formats  or what?

[Anonymous]

Hello again

I was looking for advice regarding nulls and formats. For instance, with the dates, it's on a custom format in my source file. When I transfer it to Qlik Sense however, it gets converted to numbers, which when re-translated to dates show the wrong dates.

[jonathandienst]

You may need to use the data load editor to handle unrecognized date formats. Let's say you have a date formatted like 'YYYYMMDD'  (eg 20181128). Qlik will default to treating this like the value 20,181,128.00. To get this as a date, use the date interpretation function Date#(). Something like this:

LOAD 
  ...
  Date(Date#(myDateField, 'YYYYMMDD')) as MyDate,
  ...

The outer date format function ensures that the result is in the default date format.