Date Function

[michael_anywar]

Dear, i need help here.

I want to load my data that includes date and timestamps.

When I do it on cloud, its fine. but on my desktop app it refuses.

My field ts is a time stamp. On my Qlik Sense cloud, it loads,

However on the Destop it is shown as General, hence dates are shown as a string.

If I use the Date function as you can see below, it does not load at all. the table field ts is left blank/ null.

Help please.

[TaskActivities]:

LOAD

// [ts] AS [ts],

//    Date#(`ts`,'DD.MM.YYYY') as "ts",

    Date#(`ts`,'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

[fullName] AS [fullName],

[activityType] AS [activityType],

[taskType] AS [taskType],

[manufacturingOrderId] AS [manufacturingOrderId],

[productId] AS [productId],

[taskId] AS [taskId]

RESIDENT RestConnectorMasterTable;

Thank you.

[YoussefBelloum]

EDITED

Hi,

try this:

[TaskActivities]:

LOAD

// [ts] AS [ts],

Date(Date#(`ts`,'DD.MM.YYYY'),'DD.MM.YYYY') as "date",

timestamp(timestamp#(`ts`,'DD.MM.YYYY hh:mm:ss[.fff]'),'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

[fullName] AS [fullName],

[activityType] AS [activityType],

[taskType] AS [taskType],

[manufacturingOrderId] AS [manufacturingOrderId],

[productId] AS [productId],

[taskId] AS [taskId]

RESIDENT RestConnectorMasterTable;

[OmarBenSalem]

You can't do that Youssef.

If ts field is written as follow: 'DD.MM.YYYY hh:mm:ss[.fff]'

u can't do this:

Date#(`ts`,'DD.MM.YYYY')

Qlik won't recognize it; since it's not written as follow.

and visversa

see this example :

load *,

date(Date#(ts,'DD/MM/YYYY')) as date,

timestamp(Timestamp#(ts,'DD/MM/YYYY hh:mm:ss')) as timestamp

Inline [

ts

20/02/2018 11:45:36

];

result:

Furthermore, what's the point of the 2 quotes surrounding the ts field?

I mean this :

timestamp(timestamp#(`ts`,'DD.MM.YYYY hh:mm:ss[.fff]'),'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

u'll have to eliminate those quotes for this to work

[YoussefBelloum]

you're right, i didn't pay enough attention of the format of the original field..

you can use one of them depending on the original Ts format.

if ts is a date:

Date(Date#(ts,'DD.MM.YYYY'),'DD.MM.YYYY') as "date",

if ts is a timestamp;

timestamp(timestamp#(ts,'DD.MM.YYYY hh:mm:ss[.fff]'),'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

of course remove that ' ' when calling the field on the function.

[michael_anywar]

Is there something am doing wrong, or because am just new to this..?

I below is the whole script  including screen shot am using, but still no success

Thank you all for your guidance

..............................................................................................................................

...............................................................................................................................

LIB CONNECT TO 'Task Activities';

// Action required: Implement the logic to retrieve the total records from the REST source and assign to the 'total' local variable.

Let total = 0;

Let totalfetched = 0;

Let startAt = 0;

Let pageSize = 100;

for startAt = 0 to total step pageSize

RestConnectorMasterTable:

SQL SELECT

"ts",

"fullName",

"activityType",

"taskType",

"manufacturingOrderId",

"productId",

"taskId"

FROM JSON (wrap on) "root"

WITH CONNECTION(Url%%#######");

// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source.

// Please see the documentation for "Loading paged data."

NEXT startAt;

[TaskActivities]:

LOAD

// [ts] AS [ts],

//    Date(Date#(ts,'DD.MM.YYYY')) as "ts",

    timestamp(timestamp#(ts,'DD.MM.YYYY hh:mm:ss[.fff]')) as "ts",

[fullName] AS [fullName],

[activityType] AS [activityType],

[taskType] AS [taskType],

[manufacturingOrderId] AS [manufacturingOrderId],

[productId] AS [productId],

[taskId] AS [taskId]

RESIDENT RestConnectorMasterTable;

[gerry_hdm]

have you the  Settings correctly ?

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

[michael_anywar]

Hello Gerold, this is what I have, and I believe its correct.

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='et-EE';

SET CreateSearchIndexOnReload=1;

SET MonthNames='jaan;veebr;märts;apr;mai;juuni;juuli;aug;sept;okt;nov;dets';

SET LongMonthNames='jaanuar;veebruar;märts;aprill;mai;juuni;juuli;august;september;oktoober;november;detsember';

SET DayNames='E;T;K;N;R;L;P';

SET LongDayNames='esmaspäev;teisipäev;kolmapäev;neljapäev;reede;laupäev;pühapäev';

[OmarBenSalem]

can u share ur ts field without any tranformation? what is it returning?

[gerry_hdm]

TimeStamp is not so esay

// myDatum  20180122    (YYYYMMDD)

// myZEIT 062159        (hhmmss)

timestamp(Timestamp# (  myDatum & myZEIT   ,'YYYYMMDDhhmmss' ),'DD.MM.YYYY hh:mm:ss')             as myTimestamp          // (as Num)

Text(timestamp(Timestamp# (  myDatum & myZEIT   ,'YYYYMMDDhhmmss' ),'DD.MM.YYYY hh:mm:ss'))  as myTextTimestamp //  (as Text)

[michael_anywar]

This is it without any. Type is General.