topic Re: Replace in QlikView

Replace

Thu, 02 Jan 2014 13:03:29 GMT

Header 1
2011-12-01T08:30:00
2011-12-01T09:00:00

My date field is like this...I need to get the date alone from this. I cant use DATE function here because of that "T'.

Can i use replace function for this? But don't know how to change all the values of a field..Please help.

Re: Replace

Miguel_Angel_Baeyens — Thu, 02 Jan 2014 13:05:20 GMT

Hi,

Try in you load script

Date(Date#(Replace(FieldNameHere, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield

Miguel

Re: Replace

simondachstr — Thu, 02 Jan 2014 13:06:45 GMT

Try using SubField with 'T' as delimiter.

e.g.

SubField(Header 1,'T',1) as Date

Re: Replace

Colin-Albert — Thu, 02 Jan 2014 13:11:37 GMT

There's a comma missing after 'T'

Date(Date#(Replace(FieldNameHere, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield

Re: Replace

Miguel_Angel_Baeyens — Thu, 02 Jan 2014 13:26:30 GMT

Checked thanks!

Re: Replace

Thu, 02 Jan 2014 13:36:22 GMT

Hi,

If you want date alone, you can use this coding:

=Date(Left('2011-12-01T08:30:00',10))

Rgds,

Raju

Re: Replace

Thu, 02 Jan 2014 13:45:51 GMT

Miguel,

This is the error that am getting

SQL##f - SqlState: S1000, ErrorCode: 35, ErrorMsg: [Hortonworks][Hardy] (35) Error from Hive: error code: '40000' error message: 'Error while processing statement: FAILED: ParseException line 1:30 character '#' not supported here

table:

select IntervalTime ,Date(Date#(Replace(IntervalTime, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield from DailyData

Re: Replace

Miguel_Angel_Baeyens — Thu, 02 Jan 2014 13:51:30 GMT

That's right,

I was showing a QlikView function, not a SQL function:

Table:

LOAD

IntervalTime,

Date(Date#(Replace(IntervalTime, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield;

SQL SELECT IntervalTime

FROM DailyData;

Always use a LOAD statement in QlikView.

Miguel

Re: Replace

Colin-Albert — Thu, 02 Jan 2014 13:53:17 GMT

The date expression is a Qlikview function so will need to be in the load section of the script.

table:

LOAD

IntervalTime,

Date(Date#(Replace(IntervalTime, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield ;

SQL select IntervalTime from DailyData ;

Re: Replace

preminqlik — Thu, 02 Jan 2014 13:54:17 GMT

try this

Date(Date#(SubField(Header 1,'T',1),'YYYY-MM-DD')) as Date

Prem

Re: Replace

Thu, 02 Jan 2014 14:07:29 GMT

Albert,

Am again getting error message like this

Field not found - <IntervalTime>

SQL select IntervalTime from DailyData

Re: Replace

Miguel_Angel_Baeyens — Thu, 02 Jan 2014 14:08:25 GMT

Is IntervalTime a valid field within DailyData database? Are both actual names?

Re: Replace

Thu, 02 Jan 2014 14:28:00 GMT

Am using hadoop hive, and yes the table does have the column with the same name (it is working when doing a simple select)

Re: Replace

Miguel_Angel_Baeyens — Thu, 02 Jan 2014 15:14:44 GMT

Is your driver case sensitive? If it is, you should spell the field as it is stored in the database.

If you are connecting to Hadoop I guess you already have the driver set up properly. Does it work if you do any other select?

Re: Replace

Thu, 02 Jan 2014 18:50:44 GMT

Hi Menon,

Try this,

Dev:

LOAD * Inline

[

Sal_Date

2011-12-01T08:30:00

];

LOAD SubField(Sal_Date,'T',1) as Sub_DT Resident Dev;

LOAD Date(SubField(Sal_Date,'T',1),'DD-MM-YYYY HH:mm:SS')as Sub_Con_DT Resident Dev;

LOAD Replace(Sal_Date,'T',' ') as rpl_DT Resident Dev;

Re: Replace

Fri, 03 Jan 2014 04:07:18 GMT

Yes i did the driver set up properly..

it works when i used select * from DailyData

Re: Replace

brijesh1991 — Fri, 03 Jan 2014 04:56:14 GMT

Date(Left(Date, 10) & ' ' & right(Date,8),'DD/MM/YYYY hh:mm:ss') as Date

Put below in a textbox and you can verify::

Date(Left('2011-12-01T08:30:00', 10) & ' ' & right('2011-12-01T08:30:00',8),'DD/MM/YYYY hh:mm:ss')

Re: Replace

Fri, 03 Jan 2014 05:37:03 GMT

It worked, don't know what's d issue, but giving alias to column name did the trick..Thank u guys..

LOAD

Date(Date#(Replace(Datefield, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield ;

SQL select IntervalTime as Datefield from DailyData ;