topic Re: If statement in QlikView

If statement

upaliwije — Thu, 10 Nov 2016 05:55:13 GMT

Hi Friends

I have follwing statement in my load script . Can you pls help me to write this script in one statement instead of two

IF(POL_TYPE='F',DATE(T_DATE,'DD-MM-YY')) AS CANCEL_DATE,

IF(POL_TYPE<>'F',DATE(T_DATE,'DD-MM-YY')) AS ISSUE_DATE

Re: If statement

tresB — Thu, 10 Nov 2016 06:00:57 GMT

Try like:

DATE(T_DATE,'DD-MM-YY') as Date,

IF(POL_TYPE='F','Cancel', 'Issue') as DateFlag

Now you can use this flag field in the front-end as date type.

Re: If statement

upaliwije — Thu, 10 Nov 2016 06:13:39 GMT

Thanks

My requirement is to have Issue date and cancell date in one statement

Re: If statement

Anonymous — Thu, 10 Nov 2016 06:20:21 GMT

Hi,

For the generation of complex if statements I would suggest you use this great tool that I find very useful.

qlikblog.at - Nested If Generator

Re: If statement

tresB — Thu, 10 Nov 2016 06:22:04 GMT

Could you explain with a sample data set?

Re: If statement

upaliwije — Thu, 10 Nov 2016 06:25:57 GMT

I want Issue date and cancel date come in the same row in the following table

POLICY_NO	ISSUE_DATE	CANCEL_DATE	GROSS_PREMIUM	REFUNDS
MO00111A002006	20-04-15	-	428,005	0
MO00111A002006	-	28-04-15	0	-428,005
MO00111A001992	24-04-14	-	37,405	0
MO00111A001992	-	31-07-14	0	-37,405
Total			465,410	-465,410

My syntax is as follows

LOAD // SEQ_N0,

......

IF(POL_TYPE='F',DATE(T_DATE,'DD-MM-YY')) AS CANCEL_DATE,

IF(POL_TYPE<>'F',DATE(T_DATE,'DD-MM-YY')) AS ISSUE_DATE

FROM

Re: If statement

Anonymous — Thu, 10 Nov 2016 06:28:30 GMT

Try

IF(isnull(ISSUE_DATE),CANCEL_DATE, ISSUE_DATE) as Date

Re: If statement

tresB — Thu, 10 Nov 2016 06:30:41 GMT

You can't create two fields in one statement. You should rather use the method I showed above. Take a pivot table, dimensions: POLICY_NO, DateFlag

Exp1: GrossPrem

Exp2: Rfunds

Now drag DateFlag to the right top.

Re: If statement

Anonymous — Thu, 10 Nov 2016 07:23:09 GMT

Hi,

Test:

LOAD * INLINE [

POLICY_NO, ISSUE_DATE, CANCEL_DATE, GROSS_PREMIUM, REFUNDS

MO00111A002006, 20-04-15, , 428005, 0

MO00111A002006, , 28-04-15, 0, -428005

MO00111A001992, 24-04-14, , 37405, 0

MO00111A001992, , 31-07-14, 0, -37405

];

NoConcatenate

test_temp:

LOAD POLICY_NO,

max(ISSUE_DATE) as ISSUE_DATE,

max(CANCEL_DATE) as CANCEL_DATE,

sum(GROSS_PREMIUM) as GROSS_PREMIUM,

sum(REFUNDS) as REFUNDS

Resident Test

group by POLICY_NO;

drop Table Test;

Best regards,

Cosmina