topic Re: Help in QlikView

Help

Mon, 27 Apr 2015 09:41:49 GMT

Hi Friends,

In my data base there are many products under 'CLA_CODE', within this cla_codes there a many sub products and Those are named as 'PRD_CODE'

Ex:- CLA_CODE PRD_CODE

PP SE

PP SA

PP DD

PP LI

MC 1A

MC 1B

MC 1C Like that,

In 'DD','SE','SA' and 'LI' Prd_code there are different premiums(prices) which are entitled different Commission amounts to the respective marketing force. So what i did to calculate the relevant commission according to the price level, i introduced lower and upper limits to the price and commission structure(table) was built.

Then i have build Four arguments in my basic formula to calculate 'DIRECT_ME',

IF((RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 AND ORC_COM> 0 AND CLA_CODE='PP' AND (COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2),(ME_COM),0)

1st , 2nd, 3rd arguments are in order(expected output is coming)

4th one is '(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2)'.

Even though expression got 'OK',This is not working as per my testing. Dear friends, please help me to find the mistake i have done or propose an alternative way to do this.

Sample file attached herewith for your easy reference,

Rgds,

Priyantha.

Re: Help

Mon, 27 Apr 2015 10:41:05 GMT

Urgent Please...............!!!

Re: Help

krishna20 — Mon, 27 Apr 2015 11:36:54 GMT

Hi,

What is your expected output?

Pls change condition as or in your expression and try.

IF(CAT='BR' or CLA_CODE='PP' or RANGE1<PREMUM And PREMUM<RANGE2,SUM(BR_COM),0)

Re: Help

Mon, 27 Apr 2015 11:59:24 GMT

Dear Crishna,

I want to match the three arguments at the same time. Then 'OR' is not supported instead of 'AND'.

The other thing is 'PREMUM' should be within the this range 'RANGE1<PREMUM And PREMUM<RANGE2'. problem is this part not working in the formula even the status of the expression 'OK'.

Anyway Thanks lot for the help.

Rgds,

Priyantha.

Re: Help

Mon, 27 Apr 2015 12:20:10 GMT

Use IntervalMatch !!

Re: Help

Tue, 28 Apr 2015 02:38:07 GMT

Please give some information about IntervalMatch as i'm bigginer in QV.

Re: Help

ToniKautto — Tue, 28 Apr 2015 04:59:10 GMT

Please notice that the OK in your expression editor is just a syntax validation. It does not indicate any thing about the logic in your expression, and reasonably QlikView can not determine if an expression is inline with your intentions.

For troubleshooting you need to minimize your scenario. It is simply not feasible to trouble shoot a expression with this many parameters, and also you need to break the logic down to a level where you can validate that the different components return the values you expect.

If your problem is related to the expression below, and you have identified that the bold part is the problem, you should provide a sample focusing only on the troubled part.

IF((RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 AND ORC_COM> 0 AND CLA_CODE='PP' AND(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2),(ME_COM),0)

In your chart add a new expression with the troubled part. Is the result in this column as you expect now?

(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2

If the result is not as expected, continue breaking the expression down in parts, to see where the problem comes from. Does the below to expressions return the result you expect?

(COM_PRE+SR+TC)>RANGE1

(COM_PRE+SR+TC)<RANGE2

Keep on breaking up the trouble expression until you find the logical problem, and then put everything back together piece by piece.

I think I have already recommended in a previous case that you need to provide a very precise example and scenario if you expect quick help. Your questions also need to have a proper subject so that community members can see what you need help with, the subject "help" is not very likely to attract the right person to read your question.

Re: Help

ToniKautto — Tue, 28 Apr 2015 05:01:25 GMT

If you are a beginner it is recommend that you refer to the reference manual and the help (press F1 in desktop client) for details on functions and features. Have you read the IntervalMatch section and looked at the example?

Re: Help

Tue, 28 Apr 2015 06:17:40 GMT

Dear Tony,

Thanks for the instructions and information.

I have done this and identified "(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2 " not working.

In my data base COM_PRE+SR+TC=GRO_PRE.

Because i have same Product codes with different prices i have created upper and lower price levels in my commission table and tried to work out the commissions that are entitled with the price levels.

But still i'm not in a position to find out what is the reason to not match with above logic.

Anyway thanks lot once again for your kind help.

Re: Help

Tue, 28 Apr 2015 06:18:40 GMT

Just i'm looking the help menu,

Rgds,

Priyantha.

Re: Help

Wed, 29 Apr 2015 02:42:58 GMT

Finlay i found the longest way to do this.

Thanks every one who shared the knowledge with me. I was in a requirement of doing this in short and easy way.

But i realized that everything can not be done in that manner.

IF((RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 AND ORC_COM> 0), IF(PRD_CODE='DD' AND GRO_PRE>1500 AND GRO_PRE<2050,225,IF(PRD_CODE='DD'AND GRO_PRE>2051 AND GRO_PRE<3000,250,IF(PRD_CODE='SA'AND GRO_PRE>100 AND GRO_PRE<150,20,IF(PRD_CODE='SA'AND GRO_PRE>475 AND GRO_PRE<600,60,IF(PRD_CODE='PH',100,IF(PRD_CODE='CH',30,IF(PRD_CODE='LI'AND GRO_PRE>475 AND GRO_PRE<600,60,IF(PRD_CODE='LI'AND GRO_PRE>700 AND GRO_PRE<1200,125,IF(PRD_CODE='LI'AND GRO_PRE>1201 AND GRO_PRE<1800,175,IF(PRD_CODE='SE' AND GRO_PRE>500 AND GRO_PRE<800,50,IF(PRD_CODE='SE'AND GRO_PRE>1100 AND GRO_PRE<1500,90,IF(PRD_CODE='SE'AND GRO_PRE<1501 AND GRO_PRE>1900,130,IF(PRD_CODE='PD',COM_PRE*ME_COM,0))))))))))))),0)

Rgds,

Priyantha.

Re: Help

ToniKautto — Thu, 30 Apr 2015 03:34:45 GMT

I do not think that will be a very good solution for you in the long, neither performance wise or from maintenance perspective. NEsted IF statements is never a good idea.

The recommend action is that you spend time on studying QlikView as a product, so that you find the best way of pre-calculating your data in the load script. For example IntervalMatch has bee suggested as possible way to create the intervals you now generate through a nested IF statement.

I am sure you can find a much better solution and studying existing demo applications or attending product training can be good ways forward. Also referring to manual and and searching Community can give you insight into features of QlikView. In short, good and proper data modelling is the key for a really well performing and accurate application.

Re: Help

Thu, 30 Apr 2015 03:52:54 GMT

Of cause agreed.

Actually i'm very junior in QV. and studying the product in PE version. So i learned many features in this application from the qlik community.

So i used this in my day today presentations and analyzing data. Any way this is a good tool in every aspect i found in my professional life.

Rgds.

Priyantha.