topic Dimension Related in QlikView

Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 11:48:26 GMT

Dear ALL ,

I am using simple Wildmatch function......... below is calculated dimension code..

=if(Wildmatch(D_NO,1),'Exellent',
if(Wildmatch(D_NO,2),'Very_Good',
if(Wildmatch(D_NO,3),'Good',
if(Wildmatch(D_NO,4),'Average',
if(Wildmatch(D_NO,5),'Need_Improvement',
if(Wildmatch(D_NO,1,2,3),'Fabulous'))))))

i am not getting Fabulous as dimension content.....kindly help if any tricks...

My QVW is also attached here for your reference.........

Re: Dimension Related

rubenmarin — Tue, 17 Mar 2015 11:51:44 GMT

Hi Sarfaraz, it will not get to fabolous because if D_NO =1 or 2 or 3, it will go to 'Excellent', Very good' or 'Good'

If you want to concatente that string to the first 3 results you can set that condition id different 'if' sentence:

=if(Wildmatch(D_NO,1),'Exellent',

if(Wildmatch(D_NO,2),'Very_Good',

if(Wildmatch(D_NO,3),'Good',

if(Wildmatch(D_NO,4),'Average',

if(Wildmatch(D_NO,5),'Need_Improvement'))))) &

if(Wildmatch(D_NO,1,2,3),' Fabulous')

Re: Dimension Related

datanibbler — Tue, 17 Mar 2015 11:52:24 GMT

Hi,

the WildMatch function is for matching with wildcards - so you need (or rather, you can) use the *.

Judging from that code, the values 1, 2 and 3 are already populated with "Excellent", "Very good" and "Good" - they cannot be matched against any other value, at least not in the same expression.

What is it you want to achieve with this? Knowing what you want this code to tell you would make it easier to tell you why it doesn't do that or how you could do it.

Best regards,

DataNibbler

Re: Dimension Related

maxgro — Tue, 17 Mar 2015 11:58:32 GMT

if you want the result on the right

if(Wildmatch(D_NO,4),'Average',

if(Wildmatch(D_NO,5),'Need_Improvement',

if(Wildmatch(D_NO,1,2,3),'Fabulous')))

Re: Dimension Related

rubenmarin — Tue, 17 Mar 2015 11:58:47 GMT

Or you can try adding this table to tour script:

Dimension:

LOAD * Inline [

D_NO, Dimension

1, Excellent

2, Very Good

3, Good

4, Average

5, Need Improvement

1, Fabulous

2, Fabulous

3, Fabulous

];

Then use Dimension field as dimension.

As DataNibbler saids, it's better if we know what you want to achieve.

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 12:10:19 GMT

Dear DataNibbler,

There is requirement from business user to achive like below .... Thast why i have highlighted small version of this above...

actually i have to this in my high level application ...somewhat like below

if(Wildmatch(STATUS_ID,80),'Insurer_reject',
if(Wildmatch(STATUS_ID,75),'Follow_Up',
if(wildmatch(STATUS_ID,77),'Lead Lost',
if(wildmatch(STATUS_ID,82),'Lead Pending U/W',
if(wildmatch(STATUS_ID,79),'Non Contactable',
if(wildmatch(STATUS_ID,78),'Not Interested',
if(wildmatch(STATUS_ID,99),'Pending for PPC',
if(wildmatch(STATUS_ID,74),'Sales Open Default',
if(wildmatch(STATUS_ID,87,92,94,97,91,93,90,103,121,123),'Sales closed final',

if( wildmatch(STATUS_ID,'74','75','77','78','79','80','82','87','89','92','94','97','91','93','90','99','103','114','121','123'),'Total Leads Login',
))))))))))

Sarfaraz

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 12:12:46 GMT

There is requirement from achive like this in application...

Is it possible in on chart level ..........if yes then how i can achive the same .

Sarfaraz

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 12:14:55 GMT

Actually i have to like below in my app...

if(Wildmatch(STATUS_ID,80),'Insurer_reject',
if(Wildmatch(STATUS_ID,75),'Follow_Up',
if(wildmatch(STATUS_ID,77),'Lead Lost',
if(wildmatch(STATUS_ID,82),'Lead Pending U/W',
if(wildmatch(STATUS_ID,79),'Non Contactable',
if(wildmatch(STATUS_ID,78),'Not Interested',
if(wildmatch(STATUS_ID,99),'Pending for PPC',
if(wildmatch(STATUS_ID,74),'Sales Open Default',
if(wildmatch(STATUS_ID,80,75,77,82,79,78,99,74),'Total Leads Login' ))))))))

Sarfaraz

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 12:17:56 GMT

No, I want to implement in single chart itself.......is it possible ???????????

Sarfaraz

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 12:47:01 GMT

Dear Nibbler,

Any update regarding the solution ??????

Sarfaraz

Re: Dimension Related

datanibbler — Tue, 17 Mar 2015 12:51:52 GMT

Hi Sarfaraz,

it is possible - but not in a single expression. Like I have said, the values can only be populated once - e.g., they can only be matched against one text in one expression. Once the values are "taken", they are gone.

You need a second expression (a second field) to match them against some other text.

How would you want to do in your chart? What should it look like?

Again, knowing where you want to go makes it easier for others to tell you how to get there.

Best regards,

DataNibbler

P.S.: Oh - and independent of this, if you use a PICK(MATCH()) function in your expression instead of all these nested IF_clauses, it makes the whole thing much easier to reconstruct or maintain (later or by someone else) - rgd. the bracketing. In a PICK(MATCH()) function, each line is closed off for itself, so there are not 7 closing brackets in one point ...

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 13:04:15 GMT

Dear Nibbler,

Total Leads Considered Insurer Reject Follw up So on.....

Chennai Amount 523478 17175 30000

Count 311 2 8

Mumbai Amount 500789 80000 257800 So on...

Count 239 1 7

Status_ID =75 Follow_up

Status_ID=80 Insurer Reject

Insurer Reject + Follow_up = Total Leads_considere

likewise i want to achieve ...........this is my requirement ..thats the reason i asked you is it possible or not to do so ..

Sarfaraz

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 13:06:45 GMT

Dear Nibbler,

Can you keep any example for my understanding ........so it would be eassy for me to do so ....

Sarfaraz

Re: Dimension Related

datanibbler — Tue, 17 Mar 2015 13:13:47 GMT

Hi,

I guess I understand. So you want to have two dimensions in your chart so that the user can drill down in the data from the Total into the parts?

That is not possible either in one chart or in one field (GUI-level vs. Scripting_level) - you can apply that MATCH() or WildMatch() function in the script or on the GUI, but you can only match one value with one text in one expression. So you need two similar Wildmatch() functions which will make up two dimensions - it is more resource-friendly to do so in the script, but it will work on the GUI.

With the PICK(MATCH()) it would look roughly like this:

= PICK(Wildmatch([value], 75, 80, 82),

// match value 75

'text_#1,

// match value 80

'text_#2,

// match value 82

'text_#3

)

HTH

Re: Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 13:40:29 GMT

.......any other alternate option for the same on chart level to do so ...

Sarfaraz