Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trouble Loading Data Based on Date Range

I've currently got my data script set up to load only data from 8/25/2015 and on, and I used the following script to accomplish it:

LOAD

    Title,

    Subject,

    List,

    "Send Date",

subfield([Send Date],' ',1) as Date,

subfield([Send Date],' ',2) as Time,

    "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://data*.csv]

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

where [Send Date] >= '8/25/2015';

However, I noticed when I loaded the new data for October, that the script wasn't loading anything from 10/1/2015 or later. I assume this means that my "where" function isn't correctly loading based on date.

For reference, my date data is formatted like "10/6/2015  9:02:00 AM" in the "Send Date" field and I used the answer found in this post (Splitting Date String into Time and Date Fields) to split the field into Date and Time.

What do I need to do in order to ensure my data is loading correctly?

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

Try

FROM [lib://data*.csv]

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

where Date#(subfield([Send Date],' ',1),'M/D/YYYY') >= makedate(2015,8,25);

View solution in original post

6 Replies
Clever_Anjos
Employee
Employee

FROM [lib://data*.csv]

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

where timestamp#([Send Date],'MM/D/YYYY hh:mm:ss TT') >= makedate(2015,8,25);

should solve your problem

Not applicable
Author

I'm afraid it didn't. I'm still missing all data from 10/1/2015 onward. I've posted below an example of the data, the data in September seems to load just fine

  

TitleSend Date
D10/1/2015 9:01
C10/1/2015 9:01
J9/30/2015 10:37
29/30/2015 10:00
L9/30/2015 9:24
F9/30/2015 9:01
Clever_Anjos
Employee
Employee

Try

FROM [lib://data*.csv]

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

where Date#(subfield([Send Date],' ',1),'M/D/YYYY') >= makedate(2015,8,25);

Clever_Anjos
Employee
Employee

Could you post a sample of records not beeing loaded?

Not applicable
Author

This worked. Thanks!

Clever_Anjos
Employee
Employee

Good to know