Skip to main content

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting Date String into Time and Date Fields

I have a date string in a CSV field that I currently have labeled as "Send Date" and looks like this:

9/24/2015  9:01:00 AM

I want to be able to split this date string into two fields:

9/24/2015 as "Date Sent"

9:01:00 AM as "Time Sent"

Currently my data load script looks like this:

LOAD

    Title,

    Subject,

    List,

    "Send Date",

    "Send Weekday",

    "Total Recipients",

    "Successful Deliveries",

    "Soft Bounces",

    "Hard Bounces",

    "Total Bounces",

    "Times Forwarded",

    "Forwarded Opens",

    "Unique Opens",

    "Open Rate",

    "Total Opens",

    "Unique Clicks",

    "Click Rate",

    "Total Clicks",

    Unsubscribes,

    "Abuse Complaints",

    "Times Liked on Facebook",

    "Folder Id",

    "Unique Id"

FROM [lib://Newsletter Data/Sep_25_2015.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

   

What do I need to add in order to get the date/time split I want from my source data?

1 Solution

Accepted Solutions
darkhorse
Partner - Specialist
Partner - Specialist

Hello,

Try subfield() function.

Something like

subfield([Your Field],' ',1) as Date,

subfield([Your Field],' ',2) as Time,

BR

Serhan

View solution in original post

4 Replies
darkhorse
Partner - Specialist
Partner - Specialist

Hello,

Try subfield() function.

Something like

subfield([Your Field],' ',1) as Date,

subfield([Your Field],' ',2) as Time,

BR

Serhan

jonathandienst
Partner - Champion III
Partner - Champion III

Date(Floor([Send Date])) As [Send Date],

Time(Frac([Send Date])) As [Send Time],

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

This assumes that the [Send Date] field is being recognised as a date time (numeric) and not as a string. If [Send Dat] is a string (left aligns in a list box), then you need:

Date(Floor(Timestamp#([Send Date], 'M-D-YYYY h:mm:ss TT'))) As [Send Date],

Time(Frac(Timestamp#([Send Date], 'M-D-YYYY h:mm:ss TT'))) As [Send Time],

Tweak the format string in bold so the date and time are being correctly populated.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
varaprasad0816
Creator II
Creator II

Correct Answer.

I have executed