Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
caseyjohnson
New Contributor III

Filtering load data by date in Qlik Sense

New to Qlik Sense and can't seem to find a solution for this. I want to filter (limit) the data (from an individual table/source) being loaded by a field's date (ex"sold_date"). One thing to note is the field name was modified in the load statement ... not sure how that effects the solution/syntax. The date format for that field is "YYYY-MM-DD".

LIB CONNECT TO 'PostgreSQL_database';

[oldparts]:

LOAD sold_date as "parts_sold_date",

  Year(sold_date) as "parts_sold_year",

    Month(sold_date) as "parts_sold_month",

  price_sold,

  who_sold,

  how_sold;

[oldparts]:

SELECT "sold_date",

  "price_sold",

  "who_sold",

  "how_sold",

FROM "database"."oldparts";

Where Date(sold_date) >= '2016-01-01';

Tags (1)
1 Solution

Accepted Solutions
ali_qlikview
New Contributor II

Re: Filtering load data by date in Qlik Sense

LIB CONNECT TO 'PostgreSQL_database';

[oldparts]:

LOAD sold_date as "parts_sold_date",

  Year(sold_date) as "parts_sold_year",

    Month(sold_date) as "parts_sold_month",

  price_sold,

  who_sold,

  how_sold;

SELECT "sold_date",

  "price_sold",

  "who_sold",

  "how_sold",

FROM "database"."oldparts"

Where Date(sold_date) ,= '2016-01-01';

Can you try something like above.

6 Replies

Re: Filtering load data by date in Qlik Sense

May be this:

LIB CONNECT TO 'PostgreSQL_database';


[oldparts]:

LOAD sold_date as "parts_sold_date",

  Year(sold_date) as "parts_sold_year",

    Month(sold_date) as "parts_sold_month",

  price_sold,

  who_sold,

  how_sold

Where Date(sold_date) >= MakeDate(2016, 1, 1);

SELECT "sold_date",

  "price_sold",

  "who_sold",

  "how_sold",

FROM "database"."oldparts";

caseyjohnson
New Contributor III

Re: Filtering load data by date in Qlik Sense

Thank you for the answer, however it does not seem to be working. I'm getting an unknown error on that statement when I load the data. Forgot to mention that I'm currently on the desktop version (which will be loaded to the server) ... if that matters???

Lines fetched: 100

stock << QueryResult

Lines fetched: 100

stock_parts_names << QueryResult

Lines fetched: 1,665

---

The following error occurred:

Unknown statement

---

The error occurred here:

Where Date(sold_date) > MakeDate(2016, 1, 1)

Employee
Employee

Re: Filtering load data by date in Qlik Sense

Dear Casey,

   It could be a data format of his database. Follow a link to help and understand how to convert date.

Date and time interpretation ‒ QlikView

Date ‒ QlikView

   Please, mark the CORRECT and HELPFUL comments.

Regards,

Ricardo Gerhard

Ricardo Gerhard
OEM Solution Architect
LATAM
ali_qlikview
New Contributor II

Re: Filtering load data by date in Qlik Sense

LIB CONNECT TO 'PostgreSQL_database';

[oldparts]:

LOAD sold_date as "parts_sold_date",

  Year(sold_date) as "parts_sold_year",

    Month(sold_date) as "parts_sold_month",

  price_sold,

  who_sold,

  how_sold;

SELECT "sold_date",

  "price_sold",

  "who_sold",

  "how_sold",

FROM "database"."oldparts"

Where Date(sold_date) ,= '2016-01-01';

Can you try something like above.

Gabriel
Valued Contributor III

Re: Filtering load data by date in Qlik Sense

Hi,

The Date function you are using is the presentation Date function. What I will look to do is check if the Date FORMAT (sold_date) from the source is different from how am presenting it.

You possibly might want to try something like

MAKEDATE(YEAR(sold_date))

Hope this is helpful

caseyjohnson
New Contributor III

Re: Filtering load data by date in Qlik Sense

Sorry, I had wrong information in my original post! The time format for the field I'm trying to filter is as follows

M/DD/YYYY HH:MMSmiley FrustratedS AM

So what should this look like in the load editor? Or should I reformat the original field and shorten it to a shortdate (MM/DD/YY)? If so, what would that look like?