Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Looks fine for me, Syntax and logic is correct, What issue you are facing???
REgards
Can u Pls modify my script and reply
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
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
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
If statement looks correct. What is the error message? Can you post entire script?
//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;
Hi,
Did you try with my reply.
Regards