If statement

[upaliwije]

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 

[tresesco]

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.

[upaliwije]

Thanks

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

[Anonymous]

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

[tresesco]

Could you explain with a sample data set?

[upaliwije]

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

[Anonymous]

Try

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

[tresesco]

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.

[Anonymous]

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