topic Re: Extract Data into a Bar Chart in QlikView

Extract Data into a Bar Chart

Fri, 31 Aug 2012 14:04:19 GMT

Hi,

I am trying to enter certain information into a bar chart. I have a field called Fix Code and what I am looking to do, is depending on the first four letters of the fixcode, will represent what type of issue this is.

We have the following example information in the fixcode field:

FLT0-CRPR-RCL0-SSWO

FLT0-CRPR-RCL0-STNT

RQST-NS01-SS01-TTRT

RQST-FST-RRCL-THR1

What I am looking to do is if the first four letters = FLT0, we will call it Fault

If the first four letters are = RQST, we will call it Request.

I want a bar chart that displays the total amount of Fault (FLT0) calls and the total amount of Request Calls (RQST) together.

Therefore I am hoping my Bar Chart will show 2 columns one with Fault showing say 80 calls and one with Request showing say 200 calls.

Is this Possible?

Re: Extract Data into a Bar Chart

m_woolf — Fri, 31 Aug 2012 14:11:58 GMT

Try using a calculated dimension something like this:

if(left([Fix Code],4) = 'FLT0','Fault','Request')

Re: Extract Data into a Bar Chart

Fri, 31 Aug 2012 14:17:45 GMT

Put a formula forit in the script. See attached.

Re: Extract Data into a Bar Chart

Fri, 31 Aug 2012 14:18:36 GMT

Hi mwoolf,

I have enter this as my calculated dimension, however it wont display any data as I have put nothing in my Expression. What do I need to put in my expression.

Re: Extract Data into a Bar Chart

jagannalla — Fri, 31 Aug 2012 14:19:03 GMT

Hi,

Please check the attached file.

Map1:
Mapping
LOAD * INLINE [
    Key, Value
    FLT0, Fault
    RQST, Request
];
Main:
LOAD RowNo() as ID, *,ApplyMap('Map1',Left(Fixcode,4)) as Fix;
LOAD * INLINE [
    Fixcode , Amount
    FLT0-CRPR-RCL0-SSWO,100
    FLT0-CRPR-RCL0-STNT,200
    RQST-NS01-SS01-TTRT,300
    RQST-FST-RRCL-THR1,400
];
- In st table add two expressions:
FaultCall ->      =Sum({<Fix={"Fault"}>}Amount)
RequestCall -> =Sum({<Fix={"Request"}>}Amount)

Re: Extract Data into a Bar Chart

m_woolf — Fri, 31 Aug 2012 14:20:43 GMT

coount(WhatEverFieldYouWantToCount)

Re: Extract Data into a Bar Chart

Fri, 31 Aug 2012 14:22:02 GMT

Hi rebeccad

I only have a Personal Edition Licence and have used my 4 attempts, so I am unable to view your Qlikview File.

Regards,

Jon

Re: Extract Data into a Bar Chart

whiteline — Fri, 31 Aug 2012 14:22:16 GMT

I suggest

=pick(mixmatch(left([Fix Code],4), 'FLT0', 'RQST'), 'Fault', 'Request')

for calculated dimension (or load script formula).

So that you can easily expand it with other fixcodes in future

and don't miss other fixcodes (considering them as 'Request').

Re: Extract Data into a Bar Chart

Fri, 31 Aug 2012 14:24:19 GMT

No worries. I suggested adding the following to your script:

if(left([Fix Code],4) = 'FLT0','Fault','Request') As FixCodeField

Then you can just build the graph like you normally would, but with the new field.