Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

12 Replies
Not applicable
Author

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

krishna20
Specialist II
Specialist II

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)

Not applicable
Author

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.

Not applicable
Author

Use IntervalMatch !!

Not applicable
Author

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

ToniKautto
Employee
Employee

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.

ToniKautto
Employee
Employee

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?

Not applicable
Author

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.

Not applicable
Author

Just i'm looking the help menu,

Rgds,

Priyantha.