topic Re: If Condition in QlikView

If Condition

upaliwije — Wed, 22 Apr 2015 07:35:18 GMT

Dear Friends

In my load script I have following line which does not execute

if(POL_TYPE='F' AND IsNull(LAST_PERIOD),'N',IF(POL_TYPE='F' AND (LEN(TRIM(LAST_PERIOD)))>0,'R',if(POL_TYPE='A','R',POL_TYPE)))AS POL_TYPE,

Pls see and advise me whether it is wrong. My requirement is as follows

If POL_TYPE='F' and LAST_PERIOD is null POL_TYPE should be 'N'

If POL_TYPE='F' and LAST_PERIOD is not null POL_TYPE should be 'R'

If POL_TYPE='A' POL_TYPE should be 'R'

for other records POL_TYPE=POL_TYPE as recorded in the data table

Re: If Condition

datanibbler — Wed, 22 Apr 2015 07:42:12 GMT

Hi Upali,

when you want to link two conditions with AND, you have to put the two in another pair of brackets (inside the IF_clause).

HTH

Re: If Condition

PrashantSangle — Wed, 22 Apr 2015 07:42:50 GMT

Hi,

Looks fine for me, Syntax and logic is correct, What issue you are facing???

REgards

Re: If Condition

upaliwije — Wed, 22 Apr 2015 07:46:41 GMT

Can u Pls modify my script and reply

Re: If Condition

PrashantSangle — Wed, 22 Apr 2015 07:50:04 GMT

Hi,

There might be small bracket mistake in second if condition

just check it

if(POL_TYPE='F' AND IsNull(LAST_PERIOD),'N',IF(POL_TYPE='F' AND (LEN(TRIM(LAST_PERIOD))>0),'R',if(POL_TYPE='A','R',POL_TYPE)))AS POL_TYPE,

Regards

Re: If Condition

mato32188 — Wed, 22 Apr 2015 07:50:28 GMT

Hi Upali,

(POL_TYPE='A','R',POL_TYPE)))AS POL_TYPE try to convert into

(POL_TYPE='A' OR POL_TYPE='R',POL_TYPE)))AS POL_TYPE,

Re: If Condition

datanibbler — Wed, 22 Apr 2015 07:52:39 GMT

Sure.

I have no syntax_checker, so the brackets at the end might be wrong, but I think this should work:

IF((POL_TYPE = 'F' AND IsNull(LAST_PERIOD)), 'N',

IF((POL_TYPE='F' AND LEN(TRIM(LAST_PERIOD))>0, 'R',

IF(POL_TYPE='A', POL_TYPE))) AS POL_TYPE

(as a side, it helps if you do like this and insert line_breaks inbetween the parts of your formulad. It also helps if you always start with the brackets (so you have one opening and one closing) and then insert the contents.

As you have two cases with POL_TYPE='F', you could also try reformulating that and querying that only once and inside that, put another query for that LAST_PERIOD.

If it doesn't work, you can also check if that ISNULL() function works in your case. I have made the experience that sometimes it doesn't work, then you could try LEN() (should be 0 then)

HTH

Re: If Condition

anbu1984 — Wed, 22 Apr 2015 09:02:42 GMT

If statement looks correct. What is the error message? Can you post entire script?

Re: If Condition

upaliwije — Wed, 22 Apr 2015 09:08:23 GMT

//Binary [profilt&loss.qvw];

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//set x=1

//if x <>1

//then

sales:

FIRST 100

LOAD CLA_CODE ,

PRD_CODE ,

POLICY_NO,

if (CLA_CODE='MC','MOTOR',if(CLA_CODE='M3','MOTOR',if(CLA_CODE='M4','MOTOR','NON_MOTOR')))as MONM,

//date(Date#(POL_PERIOD_FROM,'YYYYMMDD'),'M/D/YYYY')AS POL_PERIOD_FROM,

Date( MonthStart( POL_PERIOD_FROM ), 'MMM-YYYY') as POL_PERIOD_FROM,

Date( MonthStart( POL_PERIOD_TO ), 'MMM-YYYY') as POL_PERIOD_TO,

POL_SUM_INSURED,

ME_CODE,

if (BCOD='M100','NU00',if (BCOD='CN00','JF00',if(BCOD='MP00','JF00',BCOD))) as BCOD,

TRN_TYPE,

if (POL_TYPE='N', 1,if(POL_TYPE='R',1,if(POL_TYPE='F'and POL_STATUS='9',-1)))as Nos,

POL_STATUS,

REF_NO,

RC_TRN_TYPE,

POL_TYPE,

DATE(T_DATE,'DD-MM-YY')AS T_DATE , //tranaction date

TRN_DATE, //risk date

PREMIUM,

CC_CODE,

MAIN_CRED AS MAIN_CODE

FROM

E:\Qlikview\QVD\FACT_Table\Sales.qvd

(qvd)

WHERE PRD_CODE='PD';

sales1:

LOAD

CLA_CODE ,

BCOD,

POLICY_NO,

TRN_DATE,

POL_PERIOD_TO,

POL_PERIOD_FROM,

POL_SUM_INSURED,

ME_CODE,

TRN_TYPE,

POL_TYPE,

Nos,

POL_STATUS,

REF_NO,

RC_TRN_TYPE,

T_DATE,

PREMIUM,

CC_CODE,

MAIN_CODE,

if(previous(POLICY_NO)=POLICY_NO,peek(POL_PERIOD_TO))as LAST_PERIOD

//If(Peek('POLICY_NO') = POLICY_NO, Peek(POL_PERIOD_TO), POL_PERIOD_TO) as LAST_PERIOD

RESIDENT sales

//where POLICY_NO='MG0014PD0000003'

ORDER BY POLICY_NO,T_DATE asc;

DROP table sales;

sales2:

LOAD

CLA_CODE ,

BCOD,

POLICY_NO,

TRN_DATE,

POL_PERIOD_TO,

POL_PERIOD_FROM,

POL_SUM_INSURED,

ME_CODE,

TRN_TYPE,

// POL_TYPE,

Nos,

// if (POL_TYPE='F','N',POL_TYPE)AS POL_TYPE,//AND isnull(LAST_PERIOD)=0,

//,IF(POL_TYPE='F' AND (LEN(TRIM(LAST_PERIOD))>0))AS POL_TYPE,

//,'R',if(POL_TYPE='A','R',POL_TYPE)))AS POL_TYPE,

if(POL_TYPE='F' AND IsNull(LAST_PERIOD),'N',IF(POL_TYPE='F' AND (LEN(TRIM(LAST_PERIOD)))>0,'R',if(POL_TYPE='A','R',POL_TYPE)))AS POL_TYPE,

POL_STATUS,

REF_NO,

RC_TRN_TYPE,

T_DATE,

PREMIUM,

CC_CODE,

MAIN_CODE,

LAST_PERIOD

RESIDENT sales1 ;

//where POLICY_NO='MG0014PD0000003'

//ORDER BY POLICY_NO,T_DATE asc;

DROP table sales1;

Re: If Condition

PrashantSangle — Wed, 22 Apr 2015 09:16:01 GMT

Hi,

Did you try with my reply.

Regards

Re: If Condition

upaliwije — Wed, 22 Apr 2015 09:19:40 GMT

Yes

but not working

Re: If Condition

anbu1984 — Wed, 22 Apr 2015 09:28:02 GMT

What is the error message?