topic Re: TimeStamp problem in QlikView

TimeStamp problem

Anonymous — Mon, 06 Apr 2015 06:45:36 GMT

Hello,

I have a strange issue with date column.

I am able to convert date column into MM/DD/YYYY in my local machine, but I was not able do in remote desktop and it is showing null values.

I used num(date) in script.

Iam able to convert my own format using Left(date,10), but not able to convert DD/MM/YYYY format.

Is there settings to apply for remote desktop version

Please guide me how to achive this thing

Regards,

Kumar

Re: TimeStamp problem

prieper — Mon, 06 Apr 2015 06:58:59 GMT

Please post your script,

if you declare functions explicitly it should work, like

DATE(DATE#(MyDate, 'MM/DD/YYYY'), 'DD/MM/YYYY') AS MyConvertedDate

HTH Peter

Re: TimeStamp problem

Anonymous — Mon, 06 Apr 2015 07:09:14 GMT

Hi,

I tried it but it didn't work out and i am pasting the script below.

Dim_visitor:

LOAD visitor_key as site_visit_visitor_key ,

visitor_source_system_id_nk as [Visitor Source System ID nk],

visitor_id as [Visitor ID],

visitor_rackuid as [Visitor RackUID] ,

// visitor_created_by,

// visitor_created_datetime,

// visitor_updated_by,

// visitor_updated_datetime,

visitor_source_system_name as [Visitor Source System Name],

visitor_source_system_column as [Visitor Source System Column]

FROM

[dim_visitor.qvd]

(qvd);

Dim_time_temp:

LOAD time_key as site_visit_start_datetime_key ,

time_full_date as [Time Full Date],

time_day_number as [Time Day Number],

time_day_of_week as [Time Day Of Week],

time_month_number as [Time Month Number],

time_month_desc as [Time Month Desc],

time_month_abbr as [Time Month Abbr],

time_year_number as [Time Year Number],

time_quarter_number as [Time Quarter Number],

time_quarter_desc as [Time Quarter Desc],

time_business_day_number as [Time Business Day Number],

time_year_month_key as [Time Year Month Key],

time_last_day_month_flag as [Time Last Day Month Flag],

time_rec_added as [Time Rec Added],

time_rec_updated as [Time Rec Updated],

time_current_record as [Time Current Record],

time_week_year_number as [Time Week Year Number],

time_week_month_number as [Time Week Month Number],

time_day_yr as [Time Day Year],

time_day_week as [Time Day Week]

FROM

[dim_time.qvd]

(qvd);

TEMP1:

LOAD

num(max([Time Full Date])) as nummaxdate,

max([Time Full Date]) as maxdate

Resident

Dim_time_temp;

LET x=peek('nummaxdate',0,'TEMP1');

Dim_time:

LOAD site_visit_start_datetime_key ,

[Time Full Date],

[Time Day Number],

[Time Day Of Week],

[Time Month Number],

[Time Month Desc],

[Time Month Abbr],

[Time Year Number],

[Time Quarter Number],

[Time Quarter Desc],

[Time Business Day Number],

[Time Year Month Key],

[Time Last Day Month Flag],

[Time Rec Added],

[Time Rec Updated],

[Time Current Record],

[Time Week Year Number],

[Time Week Month Number],

[Time Day Year],

[Time Day Week],

MOD(($(x)-num([Time Full Date])),7) as y,

Day([Time Full Date]) as z

Resident

Dim_time_temp;

TEMP_MONTH:

LOAD

DAY([Time Full Date]) as dayofmonth

Resident

Dim_time_temp;

LET vDayOfMonth=peek('maxdate',0,'TEMP1');

LET vDay=Day($(vDayOfMonth));

DROP TABLE Dim_time_temp;

Regards,

Kumar

Re: TimeStamp problem

jagan — Mon, 06 Apr 2015 07:13:09 GMT

Hi,

Try to use Date() and Date#() like below

Syntax:

Date(Date#(DateFieldName, 'Source Data Format'), 'Required Date format')

LOAD

Date(Date#(DateFieldName, 'DD/MM/YYYY'), 'MM/DD/YYYY')

FROM DataSource;

Use above script if you are converting 'DD/MM/YYYY' to 'MM/DD/YYYY'.

Hope this helps you.

Regards,

Jagan.

Re: TimeStamp problem

peter_turner — Tue, 07 Apr 2015 09:33:42 GMT

Hello Kumar,

Peter and Jagan's suggestions should work for you.

The reason it works on you PC and not the the remote desktop could be the locale of the server and if its using UK/US or other date formatting.

For example at the start of all QV scripts will be the default formatting rules, my PC is UK so the default date format is

SET DateFormat='DD/MM/YYYY';

Re: TimeStamp problem

Anonymous — Tue, 07 Apr 2015 10:35:19 GMT

Hi Peter,

Actually I am new to qlikview. my remote box is US and already some default values are there like below

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

In QVD the date column [Time Full date] format is like YYYY-MM-DD, but I want like MM/DD/YYYY.

Can you help me, how to resolve this issue.

Regards,

Kumar

Re: TimeStamp problem

PrashantSangle — Tue, 07 Apr 2015 10:39:11 GMT

Hi,

did you try

date([Time Full Date],'MM/DD/YYYY')

date(date#([Time Full Date],'YYYY-MM-DD'),'MM/DD/YYYY'

Regards

Re: TimeStamp problem

Tue, 07 Apr 2015 10:41:37 GMT

Hi Kumar,

Try with this.....

SET DateFormat='M/D/YYYY'; change to SET DateFormat='MM/DD/YYYY';

Date(Date#(DateFieldName, 'DD/MM/YYYY'), 'MM/DD/YYYY')

Re: TimeStamp problem

Anonymous — Tue, 07 Apr 2015 10:51:02 GMT

I tried with 2 options, but no use.

Do we need to any settings for this one.

Regards

Re: TimeStamp problem

Tue, 07 Apr 2015 10:59:02 GMT

Hi Kumar,

Try like this.....

Date(Date#('8/4/12', 'M/D/YY'), 'MM/DD/YY') as dateField

Output is 08/04/12

Re: TimeStamp problem

Anonymous — Tue, 07 Apr 2015 11:08:00 GMT

Hi Avinash,

As u suggested I tried, but no use. I don't know why it is not displaying data. pls find the screen shot for date column

Re: TimeStamp problem

Tue, 07 Apr 2015 11:45:17 GMT

Hi kumar,

1) please check is date and time settings are correct in remote desktop.

2) Please let me know the datatype of field.

Re: TimeStamp problem

Anonymous — Tue, 07 Apr 2015 12:38:16 GMT

it is time stamp and find the attachment.

Re: TimeStamp problem

Tue, 07 Apr 2015 13:10:50 GMT

Hi Kumar,

First test the below function in the text object in your remote desktop, Is this displaying present date or not. Please let me know on this

=date(floor(now()))

Re: TimeStamp problem

PrashantSangle — Tue, 07 Apr 2015 13:14:31 GMT

Hi,

After loading that date field in QlikView what output you are getting??

Regards,

Re: TimeStamp problem

Tue, 07 Apr 2015 13:33:51 GMT

Hi Kumar,

After checking for the above script Please try with below functions:

date(floor(MyTimeStamp),'DD-MM-YYYY') as MyDate

date(timestamp#([Timestamp],'MM/DD/YYYY hh:mm:ss'), 'MM/DD/YYYY') as NewDate

Re: TimeStamp problem

Anonymous — Tue, 07 Apr 2015 15:52:31 GMT

For date(now()), it is giving MM/DD/YYYY format.

After loading into it is displaying data YYYY-MM-DD hh:mm:ss and even i tried with below but no use. It is giving blank

date(floor(MyTimeStamp),'DD-MM-YYYY') as MyDate

date(timestamp#([Timestamp],'MM/DD/YYYY hh:mm:ss'), 'MM/DD/YYYY') as NewDate

Regards,

Kumar