topic Re: About Dimension in QlikView

About Dimension

sarfaraz_sheikh — Mon, 16 Mar 2015 06:19:29 GMT

Dear ALL,

I have one column called as ' STATUS_ID' having following contents....

(75,77,78,80,82,87,91,93,94,100)

I am taking this in calculated dimension.like below

if(Wildmatch(STATUS_ID,80),'Insurer_Reject',if(Wildmatch(STATUS_ID,75),'Follow_Up' , if(Wildmatch(STATUS_ID,75,80),'Sales_Closed_Final')))

I am getting Insurer Reject and Follow_up as dimension content however i am not getting sales_closed_final in dimension content

What can be the reason ? ..........Is something i am doing wrong.....If it is possible then how i can do this

Kindly help

Sarfaraz

Re: About Dimension

robert_mika — Mon, 16 Mar 2015 06:33:33 GMT

The first two IF statement cancel the 3 one

Once you find 75 or 80 you expression stops here.

How does your Status ID data look like?

Re: About Dimension

sarfaraz_sheikh — Mon, 16 Mar 2015 06:40:03 GMT

Dear Robert,

there is no any specific code for Sales_closed_final. It is calculating by the combination of Insurer Reject and Follow_up code i.e 80 and 75 respectively.

Sales_closed_final=80+75 likewise

Is it possible ...if yes then how i can do this .......Please reply !!!!!!

Sarfaraz

Re: About Dimension

robert_mika — Mon, 16 Mar 2015 06:43:02 GMT

If you attached few examples it would be easier to give answer

Re: About Dimension

sarfaraz_sheikh — Mon, 16 Mar 2015 06:53:20 GMT

Suppose consider below data

Staus_ID premium

80 1000

75 500

90 400

103 700

80-Insure Reject

75-Follow Up

combination of 80 and 75 is = Sales closed Final

and my requirement is like below ..

if(Wildmatch(STATUS_ID,80),'Insurer_Reject',if(Wildmatch(STATUS_ID,75),'Follow_Up' , if(Wildmatch(STATUS_ID,75,80),'Sales_Closed_Final')))

then it should show like belew

Staus ID 80(Insurer Reject) 75(follow_up) Sales_Closed_final(80 and 75 both included)

Sum(premium) 1000 500 1500

hope the requirement is clear to you !!!!!!!!

Sarfaraz

Re: About Dimension

PrashantSangle — Mon, 16 Mar 2015 07:14:55 GMT

Hi,

For This You have to use Apply map()

Regards,

Re: About Dimension

sarfaraz_sheikh — Mon, 16 Mar 2015 07:25:36 GMT

Dear Max,

can you keep any example for my understanding .........

Its urgent ......please help !!!!!!

Sarfaraz

Re: About Dimension

PrashantSangle — Mon, 16 Mar 2015 08:00:46 GMT

Hi,

Visit below link

http://community.qlik.com/message/420768#420768

Regards

Re: About Dimension

Peter_Cammaert — Mon, 16 Mar 2015 08:20:52 GMT

If STATUS_ID contains multiple codes, just apply Boolean logic:

if (Wildmatch(STATUS_ID,75) and Wildmatch(STATUS_ID,80), 'Sales_Closed_Final',

if (Wildmatch(STATUS_ID,75), 'Follow_Up',

if (Wildmatch(STATUS_ID,80), 'Insurer_Reject', 'Some Default Value')))

If you want to test single Status values in multiple rows at the same time, first concatenate them in your script using a GROUP BY LOAD statement or use the concat() function to accomplish the same in your UI.

Re: About Dimension

Mon, 16 Mar 2015 08:50:30 GMT

hi,

PFA

Re: About Dimension

robert_mika — Mon, 16 Mar 2015 09:25:22 GMT

Maybe like that:

t1:

LOAD If([Staus_ID]=75,'Insure Reject', if([Staus_ID]=80, 'Follow Up','')) as Status,

premium

FROM

[156144.xlsx]

(ooxml, embedded labels, table is Sheet1);

join(t1)

load

If([Staus_ID]=75 or [Staus_ID]=80,'Sales closed Final', '') as Status,

premium

FROM

[156144.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: About Dimension

sarfaraz_sheikh — Mon, 16 Mar 2015 09:43:29 GMT

Hi peter,

Can you keep any example for my understanding of concatetnation function .....

Sarfaraz

Re: About Dimension

Peter_Cammaert — Mon, 16 Mar 2015 10:29:34 GMT

You were very vague about the context of your calculated dimension, so I made up some data and objects. Have a look at the object containing CalcDim2.

The basis of this solution that doesn't force you to change your Load Script is an aggregation of STATUS_IDs per (imaginary) Contract. Your document may allow you to simplify the expression, so do not hesitate and experiment.

Best,

Peter

Re: About Dimension

sarfaraz_sheikh — Tue, 17 Mar 2015 14:10:25 GMT

Dear Peter ,

I have used your logic in my app ...however it is showing wrong figures..

Please go through the attachment..

Sarfaraz

Re: About Dimension

Peter_Cammaert — Tue, 17 Mar 2015 14:52:20 GMT

In the Calculated Dimension of object Bad Figures, change every occurrence of SALARY into SSN. A Salary is not your typical dimension field, and that's what the aggr() function needs to do its job well.

Good luck,

Peter

Re: About Dimension

sarfaraz_sheikh — Tue, 17 Mar 2015 15:01:43 GMT

Dear Peter,

I have done the same what u told to do me ........However this time the dimension content 'Fabulous' has Not coming

Please find the attached QVW here.

Sarfaraz

Re: About Dimension

Peter_Cammaert — Tue, 17 Mar 2015 15:21:49 GMT

Then tell me what exactly you are qualifying as 'Fabulous' in your example document. Emplyees? No employee seems to have more than one D_NO. Locations? There are no locations that have D_NOs of both 1 and 2.

This case is pretty different from your initial question.

Re: About Dimension

sarfaraz_sheikh — Wed, 18 Mar 2015 06:07:32 GMT

Dear Peter,

Fabulous is combination of both 1 and 2 D_NO ..........Whatever the salary D_NO 1 and 2 have will be the addition and appear as Fabulous ....

That is only i want ...

Sarfaraz