Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Employee
Employee

Re: Trouble Loading Data Based on Date Range

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);

6 Replies
Employee
Employee

Re: Trouble Loading Data Based on Date Range

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

Re: Trouble Loading Data Based on Date Range

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
Employee
Employee

Re: Trouble Loading Data Based on Date Range

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);

Employee
Employee

Re: Trouble Loading Data Based on Date Range

Could you post a sample of records not beeing loaded?

Not applicable

Re: Trouble Loading Data Based on Date Range

This worked. Thanks!

Employee
Employee

Re: Trouble Loading Data Based on Date Range

Good to know

Community Browser