topic Ho to avoid loading of data with null datetime in QlikView

Ho to avoid loading of data with null datetime

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to load data with Casenum,Startdate(datetime),enddate(datetime), i want to drop the coulumns with null datetime while loading, can anyone help me to solve this.

Best Regards,

Arjin.

AGMT_NUM	Start_date	End_Date
160429055		29Apr2016 14:07:19
160429055	29Apr2016 14:07:19
160429055	29Apr2016 14:07:51	29Apr2016 14:07:59
160429055	29Apr2016 14:07:59	09May2016 14:52:22
160429055		09May2016 15:45:06
160429055	09May2016 15:45:06	09May2016 16:36:22
160429055	09May2016 16:36:22	10May2016 17:53:49
160429055		10May2016 17:55:50
160429055	10May2016 17:55:50	10May2016 18:11:46
160429055	10May2016 18:11:46	10May2016 18:14:38
160429055	10May2016 18:14:38
160429055	11May2016 9:15:34	11May2016 10:28:28
160429055		11May2016 12:12:52
160429055	11May2016 12:12:52	11May2016 12:13:19
160429055		11May2016 12:14:45

Re: Ho to avoid loading of data with null datetime

muthukumar77 — Thu, 16 Jun 2016 09:27:29 GMT

Hi,

Load

Casenum,

Startdate,

enddate

from

table

where len(trim(Startdate))<>0 and len(trim(enddate))<>0;

I hope it will be helpful.

Re: Ho to avoid loading of data with null datetime

Gysbert_Wassenaar — Thu, 16 Jun 2016 09:28:55 GMT

Perhaps like this:

MyTable:

LOAD

AGMT_NUM,

Start_date,

End_Date

FROM

...source...

WHERE

Len(Trim(Start_date))>0 AND Len(Trim(End_Date))>0

;

Re: Ho to avoid loading of data with null datetime

Anonymous — Thu, 16 Jun 2016 09:38:38 GMT

Hi,

Thanks for your reply, i am getting the below error when used ur suggestion

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'TRIM' keyword.

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE Len(Trim(STARTDT))<>0

Re: Ho to avoid loading of data with null datetime

Anonymous — Thu, 16 Jun 2016 09:38:59 GMT

Hi,

Thanks for your reply, i am getting the below error when used ur suggestion

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'TRIM' keyword.

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE Len(Trim(TV_CHECKED_1ST_DTTM))>0

Re: Ho to avoid loading of data with null datetime

qlikview979 — Thu, 16 Jun 2016 09:47:07 GMT

Hi Arjin,

help full to you

T1:

load * Inline [

AGMT_NUM, Start_date , End_Date

160429055 , ,29Apr2016 14:07:19

160429055 ,29Apr2016 14:07:19 ,

160429055, 29Apr2016 14:07:51, 29Apr2016 14:07:59

160429055, 29Apr2016 14:07:59, 09May2016 14:52:22

160429055, ,09May2016 15:45:06,

160429055, 09May2016 15:45:06, 09May2016 16:36:22

160429055, 09May2016 16:36:22, 10May2016 17:53:49

160429055, 10May2016 17:55:50,

160429055, 10May2016 17:55:50 ,10May2016 18:11:46

160429055, 10May2016 18:11:46, 10May2016 18:14:38

160429055, 10May2016 18:14:38,

160429055, 11May2016 9:15:34, 11May2016 10:28:28

160429055, ,11May2016 12:12:52,

160429055, 11May2016 12:12:52, 11May2016 12:13:19

160429055, ,11May2016 12:14:45,

] Where len(Trim(Start_date))>0 and Len(Trim(End_Date))>0;

Re: Ho to avoid loading of data with null datetime

Anonymous — Thu, 16 Jun 2016 10:01:29 GMT

Hi Mahesh,

it works perfect with the sample data & script, bu t when i apply it in my data its giving error,

ERROR MESSAGE{SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'TRIM' keyword.

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE len(Trim(START_DTTM))>0 and Len(Trim(END_DTTM))>0}

please advise if my script is wrong.

LEAPS_MASTER:

LOAD

CASE_NUM,

END_DTTM- START_DTTM as [TAT HRS],

if((END_DTTM- START_DTTM) <= .1666666666,Dual('0 to 4 Hours',1),

if((END_DTTM- START_DTTM) > .1666666666 AND (END_DTTM- START_DTTM) <= .3333333333, Dual('4 to 8 Hours',2),

if((END_DTTM- START_DTTM) > .3333333333 AND (END_DTTM- START_DTTM) <= .5000000000, Dual('8 to 12 Hours',3),

if((END_DTTM- START_DTTM) > .5000000000 AND (END_DTTM- START_DTTM) <= 1.0000000000, Dual('12 to 24 Hours',4),

if((END_DTTM- START_DTTM) > 1.0000000000, Dual('>24 Hours',5)))))) as [TAT Bucket],

if((END_DTTM- START_DTTM)<= .3333333333,1,0) as [SLA BUCKET],

'Last Submit to First TV Checked' AS [CS OUTPUT],

'MLTV' AS TEAM,

SUBMIT_SALES_LEAPS_ID,

SUBMIT_SALES_GROUP,

'MLTV' & WeekEnd(END_DTTM) as KEY,

MakeTime(Hour(START_DTTM), Minute(START_DTTM), Second(START_DTTM)) as [TV ARRIVAL TIME],

HL_TL_IND,

PROPERTY_TYPE,

capitalize (TVO_NAME) as [TV OFFICER];

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE len(Trim(START_DTTM))>0 and Len(Trim(END_DTTM))>0;;

Re: Ho to avoid loading of data with null datetime

tamilarasu — Thu, 16 Jun 2016 10:02:05 GMT

Re: Ho to avoid loading of data with null datetime

antoniotiman — Thu, 16 Jun 2016 10:03:28 GMT

Try

Select *

From ....

Where Not YouField is Null

Re: Ho to avoid loading of data with null datetime

muthukumar77 — Thu, 16 Jun 2016 10:04:26 GMT

Hi,

You can use in SQL command,

.......

......

Where STARTDT is NOT NULL;

Re: Ho to avoid loading of data with null datetime

muthukumar77 — Thu, 16 Jun 2016 10:15:46 GMT

In last line should be there,

WHERE START_DTTM is NOT NULL and END_DTTM is NOT NULL;

Re: Ho to avoid loading of data with null datetime

michael_klix — Thu, 16 Jun 2016 10:28:45 GMT

at least it looks much easier... why should we use the complex version ?

my version would be

if both dates must be available

WHERE(not IsNull(date1) and not IsNull(date2))

if only one date is needed

WHERE(not IsNull(date1) or not IsNull(date2))

Re: Ho to avoid loading of data with null datetime

Anonymous — Thu, 16 Jun 2016 10:33:38 GMT

Maybe try amending the load part of your script to just

LEAPS_MASTER:

LOAD

CASE_NUM,

and commect out the other fields in your load and make sure this loads using your where statement. Once it is loading you can add your other date fields etc. Just to make sure your statement is correctly coded.

Re: Ho to avoid loading of data with null datetime

ramasaisaksoft — Thu, 16 Jun 2016 11:09:15 GMT

Hi Arjin,

You told the below condition is working fine in Inline Load

Where len(Trim(Start_date))>0 and Len(Trim(End_Date))>0

I have a doubt about your ODBC Connection so please check it first.

Case 1:- if you tried with your DB it is throwing error means,First you can check without where condition ur query fetching the data from your database or not?

if it is fetches then this will also work.