topic Cleaning Up Data and Adding Catagories in QlikView

Cleaning Up Data and Adding Catagories

Anonymous — Thu, 14 Apr 2016 22:56:47 GMT

Good afternoon everyone,

My company is working with an application tied to our database that is powered by QlikView©. The developer of the application referred us to this website to access the resources here. The application is not yet supported by the developer and I believe it may still be in the beta testing phase.

In Excel, I can slice and dice information relatively easy. It would be much faster to build expressions in the application we have because exporting into Excel is time consuming and I have to do it multiple times. The QlikView update is minimal in comparison.

To make a long story short, I have been working for days trying to find an expression that will categorize information in the application. It is all based on QlikView expressions, so I thought I would ask my question here.

My field contains data formatted like: ab, ab/cd, cd/ab, and ab/hijkl

My goal is to create categories (100 for ab, 35 for ab/cd & ab/hijkl, 18 for cd/ab) and summarize the data in the numeric categories.This would replace and group the string text ab, ab/cd, etc. In Excel, I used this the replace/find/left/right functions to produce the results I am looking for (it would find the characters position and replace it). Does anyone know how I could phrase my expression in QlikView to give me a similar or better result?

Thank you!

Re: Cleaning Up Data and Adding Catagories

sunny_talwar — Fri, 15 Apr 2016 16:41:30 GMT

Do you have sample you can share with your desired output

Re: Cleaning Up Data and Adding Catagories

swuehl — Fri, 15 Apr 2016 16:45:13 GMT

You could use a MAPPING approach:

Data Cleansing

MAP:

MAPPING LOAD * INLINE [

F1,F2

ab,100

ab/cd, 35

ab/hijkl, 35

cd/ab,18

LOAD

YourField,

ApplyMap('MAP', YourField) as YourGroup,

...

FROM ...;

Re: Cleaning Up Data and Adding Catagories

Anonymous — Mon, 18 Apr 2016 15:52:00 GMT

Something like this:

Percentage	Sales	Margin	7.5% of Sales Margin
100%	$93,717.70	$41,790.94	$3,134.32
80%	$43,285.34	$10,229.29	$767.20
20%	$1,477.64	$332.25	$24.92

Thank you!

Re: Cleaning Up Data and Adding Catagories

Anonymous — Mon, 18 Apr 2016 15:59:04 GMT

Thank you for responding. The application is limited - I can only add tables and charts. I have to add expressions to the tables and charts to summarize the data. I can group, but only within a table or chart dimension. Scripting and programming are restricted.

Re: Cleaning Up Data and Adding Catagories

sunny_talwar — Mon, 18 Apr 2016 16:45:26 GMT

This is what you need, right? Can you share the data you used to reach this output?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Cleaning Up Data and Adding Catagories

Anonymous — Mon, 18 Apr 2016 17:49:54 GMT

Good morning, the table I sent you is the end result from what I did in Excel. The developer has access to the reduction and data scrambling restricted. I have uploaded a draft of the QVR document I have been working on. I can share it with you if you like, I believe it will be the report only and not the actual data though. Please add me as a connection and I will share the file with you.

Thank you!

Re: Cleaning Up Data and Adding Catagories

swuehl — Mon, 18 Apr 2016 18:00:35 GMT

If you can't change the script, you can use calculated dimensions in your chart like

=Pick(Match( YourField, 'ab','ab/cd','ab/hijkl','cd/ab')

100,

35,

)

Or using other string functions / if() statement combinations. You could for example use Wildmatch() with wildcards to shorten above a bit

=Pick( WildMatch( YourField, 'ab','ab/*','cd/ab')

100,

35,

)