topic If Condition in QlikView

If Condition

upaliwije — Wed, 13 Aug 2014 09:33:28 GMT

Hi Friends

I have written following code in my load script and data gets loaded without ant interruption. But in Paid_Range Dimension no data is available. Pls see what mistake I have done in my script

CLAIMS:

LOAD *,

if((PAID_AMOUNT-SALVAGE)<10000, Dual('<=10000',1),

if((PAID_AMOUNT-SALVAGE)>=10001 AND (PAID_AMOUNT-SALVAGE)<=25000, Dual('10,001-25,000',2),

if((PAID_AMOUNT-SALVAGE)>=25001 AND (PAID_AMOUNT-SALVAGE)<=50000, Dual('25,001-50,000',3),

if((PAID_AMOUNT-SALVAGE)>=50001 AND (PAID_AMOUNT-SALVAGE)<=100000, Dual('50,001-100,000',4),

if((PAID_AMOUNT-SALVAGE)>=100001 AND (PAID_AMOUNT-SALVAGE)<=250000, Dual('100,001-250,000',5),

if((PAID_AMOUNT-SALVAGE)>=250001 AND (PAID_AMOUNT-SALVAGE)<=500000, Dual('250,001-500,000',6),

if((PAID_AMOUNT-SALVAGE)>=500001 AND (PAID_AMOUNT-SALVAGE)<=750000, Dual('500,001-750,000',7),

if((PAID_AMOUNT-SALVAGE)>=750001 AND (PAID_AMOUNT-SALVAGE)<=1000000, Dual('750,001-1,000,000',8),

if((PAID_AMOUNT-SALVAGE)>=1000001 AND (PAID_AMOUNT-SALVAGE)<=2000000, Dual('1,000,001-2,000,000',9),

if((PAID_AMOUNT-SALVAGE)>=2000001 AND (PAID_AMOUNT-SALVAGE)<=3000000, Dual('2,000,001-3,000,000',10),

if((PAID_AMOUNT-SALVAGE)>=3000001 AND (PAID_AMOUNT-SALVAGE)<=5000000, Dual('3,000,001-5,000,000',11),

if((PAID_AMOUNT-SALVAGE)>=5000001 , Dual('Above-5,000,001',9)))))))))))))As PAID_RANGE

Resident CLAIM_PAID;

Drop table CLAIM_PAID;

Re: If Condition

tresB — Wed, 13 Aug 2014 09:44:11 GMT

Perhaps a bracket inappropriation issue. Please recheck that. Meanwhile, let me suggest a better approach. For such nested if you take top-down approach your if clause could be much lighter without AND like:

If( Value>=1000, '>=1000', If( Value>=500, '>=500<1000', If(Value>=100, '>=100<500' , '<100' )))

rather than,

if(Value<100, '<100', If(Value>=100 AND Value<500, '>=100<500' , .....

Re: If Condition

sudeepkm — Wed, 13 Aug 2014 10:03:06 GMT

your code should work. can you please make sure you do not have anything wrong in the expression used in your chart.

Are you able to see the Field PAID_RANGE in the UI. Bring it into a list box and see if the values are displayed.

Re: If Condition

upaliwije — Wed, 13 Aug 2014 10:10:37 GMT

Thanks Sudeep

The Field Paid_Range can be brought to the list box but no values available

Re: If Condition

sudeepkm — Wed, 13 Aug 2014 10:14:27 GMT

that's strange. Can you please check if your PAID_AMOUNT-SALVAGE field values are numbers what you used in the if else. e.g. 10001, 25000.

Or can you please post a sample document here.

Re: If Condition

upaliwije — Wed, 13 Aug 2014 10:36:31 GMT

Hi Sudeep

I found out the error. That is in some records Salvage is a null value so I want them to be converted to 0 value before If condition. Pls let me know ho to make Null value to 0 in the load script itself

Re: If Condition

tresB — Wed, 13 Aug 2014 10:40:33 GMT

NullAsValue PAID_AMOUNT-SALVAGE;

Set NullValue = '0';