Help

[#1]

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.

[#2 · Accepted solution]

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.

[ToniKautto]

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.

[#4]

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.