topic How to clean data in a field? in QlikView

How to clean data in a field?

Tue, 26 Jan 2016 13:57:08 GMT

Hello to all,

I'm facing some issues regarding 'dirt data' in my Dashboard.

In past scripts, I used to have two names for the same product.

Example:

In a historical database (excel spreadsheet) I had the name, for the field [Product] 'PRODUCT A'.

In my qvd database I have, for the field [Product] 'Product a'.

After I noticed that, I cleaned the database that was in the excel spreadsheet, so now in both databases the name is 'Product a'.

But the 'old name' still appears in my list box.

How can I prevent that to happen? I only want the list box to display 'Product a'

Thanks a lot folks!!!!

Re: How to clean data in a field?

maxgro — Tue, 26 Jan 2016 20:26:16 GMT

I think is better to look at the script and remove the "wrong" PRODUCT A

In listbox you can use this expression to remove PRODUCT A

if(Product='PRODUCT A', null(), Product)

Re: How to clean data in a field?

swuehl — Tue, 26 Jan 2016 22:45:34 GMT

If you really cleaned all sources to have matching values and you have performed a full reload, this should not happen.

I assume that latter has not happened, it seems that you did not a full reload, you seem to have kept some historic data within your reload chain. This could happen when you are using QVDs with an incremental load concept, for example, or when you are using a BUFFER LOAD prefix (also storing previous value state in QVDs).

Or maybe something else similar along above lines.

Hard to tell without knowing your script(s) and reload approach.

Re: How to clean data in a field?

Fri, 29 Jan 2016 12:13:10 GMT

Thanks MG,

As the number of fields to clean was low, I used this procedure, but as the data comes from a BI system, it's not advisable to use this due to maintainance reviewing process, but it is working properly for noew.

Thanks!!!

Re: How to clean data in a field?

Fri, 29 Jan 2016 12:27:11 GMT

Thanks for you comments swuehl.

My data model is 'simple'.

I have 4 QVDs which 3 of then load that from spreadsheet and another from a datawarehouse.

As I'm not using any complex LOAD form, I believe that I'm not using incremental load, but a full reload each time.

I'll look for those other posibilities you pointed out.

Thanks!!!

Re: How to clean data in a field?

hic — Fri, 29 Jan 2016 15:05:28 GMT

I would use an Applymap() instead. It is more manageable. See Data Cleansing.

HIC