topic Re: Multiple Conditions in QlikView

Multiple Conditions

Tue, 10 Dec 2013 15:16:14 GMT

Hi,

I have a loads of data and the sample is as below and the coloumn heading is <CASE expression> . I want to draw a pie chart using the following statuses. I want to show just three types of applications in the chart:

Sanctioned (i.e. Sanctioned = Sanctioned-not Booked + Booking Started + Lender Decision)
Declined
Booked

<CASE expression>

ENTERED

DECLINED

Booking Started

BOOKED

DECLINED

BOOKED

Lender Decision

BOOKED

DECLINED

BOOKED

SYSTEM DECISION

BOOKED

Sanctioned-Not Booked

REFERRAL EVENT

BOOKED

DECLINED

Cancelled

BOOKED

DECLINED

BOOKED

Cancelled

BOOKED

Lender Decision

BOOKED

Cancelled

BOOKED

REFERRAL EVENT

BOOKED

DECLINED

BOOKED

Sanctioned-Not Booked

Re: Multiple Conditions

fdelacal — Tue, 10 Dec 2013 15:30:30 GMT

i recommend you that do that in script.

If (yourfile = 'Sanctioned-not Booked', 'Sanctioned',

If (yourfile = 'Booking Started', 'Sanctioned',

If (yourfile = 'Lender Decision', 'Sanctioned')))

then in layout you count yourfile.

Hope that helps you

Re: Multiple Conditions

Tue, 10 Dec 2013 15:33:14 GMT

I need to use expression. Also need to show declined and booked apps

Re: Multiple Conditions

fdelacal — Tue, 10 Dec 2013 15:39:20 GMT

sorry but i don´t undesrtand you, can you please atach a qvw example

Re: Multiple Conditions

wms_manis — Tue, 10 Dec 2013 16:01:42 GMT

You can use the MixMatch() statement in a calculated dimension.

if(mixmatch(Status,'Sanctioned-not','Booked','Booking Started','Lender Decision')>0,'Sanctioned',Status)

See attached Example.

Re: Multiple Conditions

Tue, 10 Dec 2013 16:07:52 GMT

Hi Shoaib

In the script or calculated dimension use

if([field name] = 'booked' or [field name]= 'declined', [field name], 'sanctionned')

then in expression count(any field) and tick "relative" to have %

best regards

chris

Re: Multiple Conditions

Tue, 10 Dec 2013 16:13:35 GMT

Please tell me step by step . I am new here

Re: Multiple Conditions

wms_manis — Tue, 10 Dec 2013 16:22:54 GMT

in the front in you can use this as the calculated dimension.

=if(mixmatch(Status,'Sanctioned-not','Booked','Booking Started','Lender Decision')>0,'Sanctioned',Status)

Mixmatch() is the closest thing to a case statement in QlikView.

if it finds a match in the strings delimited by a , it returns its position in the list. If it does not find the position, it returns a 0.

In the script, another table that links to your facts could be used all well.

Re: Multiple Conditions

Josh_Good — Tue, 10 Dec 2013 21:46:10 GMT

The first thing you need to do is to group the statuses how you want them. This can be done in the load script using the following expression:

    if(Upper(Status) = 'SANCTIONED-NOT BOOKED'
            or Upper(Status) = 'BOOKING STARTED'
            or Upper(Status) = 'LENDER DECISION',
            'Sanctioned', Capitalize(Status)) as [Macro Status],

I also added in the 'Capitalize' function and 'Upper' functions to account for difference in case.

Then in the pie chart you will need to create an expression that only includes Sanctioned, Declined and Booked. This is done using set analysis. Here is the expression:

Sum({$<[Macro Status] = {'Sanctioned', 'Declined', 'Booked'}>} Amount)

Note: I assumed you wanted to add up another column which I called 'Amount'

See the attached files to see the above expressions in context.

Re: Multiple Conditions

Wed, 11 Dec 2013 17:40:39 GMT

Thanks everybody