Date-Time formatting

[#1]

Hi all,

I am importing data that includes date-time data, as follows:

Qualify *;

[MALN]:

LOAD  [submissiondate],

[start]

...

[KEY]

FROM ...

[start] is a date-time with formatting 'DD/MM/YYYY  hh:mm:ss'

I sense that QLIK Sense "sort of" recognizes this as a date, but not totally.

I want to make a bar chart with this data, with the date along the x-axis (as the dimension), and the count of [KEY] as the measure.

So I put the expression: =date(daystart([MALN.start],'DD/MM/YY')) for the x-axis.

And it just gives me a null value for date "-" - so all entries in the table show up counted for "-" date.

I've tried everything I could find in the QLIK Community for bringing the [start] in as a date (i.e., date#(start, 'DD/MM/YYYY  hh:mm:ss')), but I am at a loss.  If anyone can help out at all, or if you have any ideas, I would be so appreciative.

Many thanks,

Janna

[quriouss]

Date(Floor (Date#( start,  'DD/MM/YYYY  hh:mm:ss')) , 'DD/MM/YYYY')

• Date#() is used for reading a date format
• Date() is used for defining the internal date format for display.

What you're saying is "Read the Date in this format, Floor() it to get rid of the time, then display it in this other format"

(You might not want to get rid of the time-part. Timestamp#() will work in a similar fashion with timestamps).

[jonathandienst · Accepted solution]

Excel is probably changing the format of the dates based on ts own date recognition. For the CSV file, you need to check the original date format using a text editor (notepad will do if you have nothing else) and change your interpretation to match that format.

[#4]

I would Create a New Section to the Load Script

So I would add something like :

Tag Field

[Start]

With '$date';

Calendar:

DECLARE FIELD DEFINITION TAGGED '$date'

   Parameters

      first_month_of_year = 1

   Fields

      Year($1) As Year Tagged '$year',

      MonthName($1) as Month Tagged '$month',

      QuarterName($1) as Quarter Tagged '$quarter',

      Date($1) as Date Tagged ('$date', '$day'),

      Date(daystart($1)) as Day Tagged ('$date', '$day'),

      WeekStart($1) as Week Tagged '$week',

      Weekday($1) as Weekday Tagged '$weekday',

      DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM EXPLICIT TAGS '$date' USING Calendar;  

this will then split "start" into a number of fields to choose from so it will make it split into

Start.Calender.Day

Start.Calendar.Week

Start.calendar.Month

ect

this makes it alot easier to make tables because than you can do it by month, Week, Year, ect it should work for CSV files as well.

hope this helps

[brunobertels]

Hi

May be you can try with a dual expression that's mixxing date and date# :

Alt(

    Date(Floor(Date#("start",'DD/MMMM/YYYY hh:mm')),'DD/MM/YYYY'),

    Date(Floor(Date("start",'DD/MMMM/YYYY hh:mm')),'DD/MM/YYYY')

    ) as "Date",

[#6]

Jonathan,

You're a superstar - this solved my problem!  I was looking at the csv file in Excel, and using the date#() function with the format being displayed there.  Once I took a look at the data in Notepad++...figured out it came out a little differently.  So corrected it to match the .csv format, and voila!  It works now!

Thanks so much for your help!

Janna