If Condition

[upaliwije]

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

[datanibbler]

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

[PrashantSangle]

Hi,

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

REgards

[upaliwije]

Can u Pls modify my  script and reply

[PrashantSangle]

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

[mato32188]

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,

M

[datanibbler]

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

[anbu1984]

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

[upaliwije]

//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;

[PrashantSangle]

Hi,

Did you try with my reply.

Regards