Multiple Conditions

[#1]

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:

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

<CASE  expression>

ENTERED

DECLINED

Booking Started

BOOKED

DECLINED

DECLINED

BOOKED

BOOKED

Lender Decision

BOOKED

DECLINED

DECLINED

DECLINED

BOOKED

SYSTEM DECISION

BOOKED

Sanctioned-Not Booked

REFERRAL EVENT

BOOKED

DECLINED

DECLINED

Cancelled

BOOKED

DECLINED

BOOKED

Cancelled

BOOKED

Lender Decision

BOOKED

Cancelled

BOOKED

BOOKED

BOOKED

BOOKED

BOOKED

BOOKED

REFERRAL EVENT

BOOKED

DECLINED

BOOKED

BOOKED

Sanctioned-Not Booked

[fdelacal]

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

[#3]

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

[fdelacal]

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

[wms_manis]

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.

[#6]

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

[#7]

Please tell me step by step . I am new here

[wms_manis]

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.

[Josh_Good]

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.

[#10]

Thanks everybody