topic Re: SQL Query statement to QlikView in QlikView

SQL Query statement to QlikView

sona_sa — Wed, 08 Jul 2015 04:43:44 GMT

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.

Re: SQL Query statement to QlikView

maxgro — Wed, 08 Jul 2015 05:23:00 GMT

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

;

Re: SQL Query statement to QlikView

Wed, 08 Jul 2015 05:27:20 GMT

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

Re: SQL Query statement to QlikView

sona_sa — Wed, 08 Jul 2015 05:48:35 GMT

Hi Jemimah,

I got this output by this code.

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

Thanks for reply.

Re: SQL Query statement to QlikView

sona_sa — Wed, 08 Jul 2015 06:27:49 GMT

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.

Re: SQL Query statement to QlikView

Wed, 08 Jul 2015 07:04:19 GMT

Hi,

Please find the qvw.

Is this what you require or something else.