SQL Query statement to QlikView

[sona_sa]

How I can write this SQL Case Statement in QlikView :

(CASE  WHEN date_approved IS NULL OR TRIM(date_approved) = '0000-00-00' THEN '01/01/1901'  ELSE date_approved END)       Newdate_approved,

(CASE  WHEN date_conditionally IS NULL OR TRIM(date_conditionally) = '0000-00-00' THEN '01/01/1901'  ELSE date_conditionally END)   Newdate_conditionally,

(CASE  WHEN date_disputed IS NULL OR TRIM(date_disputed) = '0000-00-00' THEN '01/01/1901'  ELSE date_disputed END)       Newdate_disputed,

(CASE  WHEN date_reject IS NULL OR TRIM(date_reject) = '0000-00-00' THEN '01/01/1901'  ELSE date_reject END)         Newdate_reject,

Thanks.

[maxgro]

1)

load

     .....,

     if (len(trim(date_approved))=0 or trim(date_approved)='0000-00-00', makedate(1901), date_approved)

          as date_approved,

     // same for other 3 conditions

     .....;

sql select

     ....,

     ....;

I have some doubt on the bold part because it depends on your database and date format;

2) another way is to copy your sql statement in Qlik

load *;

sql select

     ....,

     (CASE  WHEN date_approved IS NULL OR TRIM(date_approved) = '0000-00-00' THEN '01/01/1901'  ELSE           date_approved END)       Newdate_approved,

     (CASE  WHEN date_conditionally IS NULL OR TRIM(date_conditionally) = '0000-00-00' THEN '01/01/1901'  ELSE           date_conditionally END)   Newdate_conditionally,

     (CASE  WHEN date_disputed IS NULL OR TRIM(date_disputed) = '0000-00-00' THEN '01/01/1901'  ELSE           date_disputed END)       Newdate_disputed,

     (CASE  WHEN date_reject IS NULL OR TRIM(date_reject) = '0000-00-00' THEN '01/01/1901'  ELSE date_reject           END)         Newdate_reject,

     .....

from

     sometableinyourdb

     ;

[#3]

Hi,

Try the below code:

if(len(date_approved)=0 OR Date(date_approved,'YYYY-MM-DD')=Date('0000-00-00','YYYY-MM-DD'),Date('01/01/1901','DD/MM/YYYY'),date_approved) AS Newdate_approved,

if(len(date_conditionally)=0 OR Date(date_conditionally,'YYYY-MM-DD')=Date('0000-00-00','YYYY-MM-DD'),Date('01/01/1901','DD/MM/YYYY'),date_conditionally) AS Newdate_conditionally,

if(len(date_disputed)=0 OR Date(date_disputed,'YYYY-MM-DD')=Date('0000-00-00','YYYY-MM-DD'),Date('01/01/1901','DD/MM/YYYY'),date_disputed) AS Newdate_disputed,

if(len(date_reject)=0 OR Date(date_reject,'YYYY-MM-DD')=Date('0000-00-00','YYYY-MM-DD'),Date('01/01/1901','DD/MM/YYYY'),date_reject) AS Newdate_reject

[sona_sa]

Hi Jemimah,

I got this output by this code.

Please find the attached xls. for output. Please revert for any query.

Thanks for reply.

[sona_sa]

Hi Jemimah,

Please find the attached QVD, There is entire data and field, On which I am trying to make some new field.

(CASE  WHEN date_approved IS NULL OR TRIM(date_approved) = '0000-00-00' THEN '01/01/1901'  ELSE           date_approved END)       Newdate_approved,

(CASE  WHEN date_conditionally IS NULL OR TRIM(date_conditionally) = '0000-00-00' THEN '01/01/1901'  ELSE           date_conditionally END)   Newdate_conditionally,

(CASE  WHEN date_disputed IS NULL OR TRIM(date_disputed) = '0000-00-00' THEN '01/01/1901'  ELSE           date_disputed END)       Newdate_disputed,

(CASE  WHEN date_reject IS NULL OR TRIM(date_reject) = '0000-00-00' THEN '01/01/1901'  ELSE date_reject           END)         Newdate_reject.

I Hope you got my point.

Thanks.

[#6 · Accepted solution]

Hi,

Please find the qvw.

Is this what you require or something else.