Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck110979
Valued Contributor III

data query (with timestamp)

Hi Folks,

i have a question: is that possible to make the query on the base of field: for instance 08.02.2017 08:15:03, by that i mean i want to make the query during the first load (without resident load) :

my situatation does look like:

Main_LOHN_LPE:

LOAD "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME"#

     "AUDIT_TIME";

SQL SELECT "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME",

      "AUDIT_TIME"

FROM "LOHN_LPE";

the field "AUDIT_TIME" consists of this value: 08.02.2017 08:15:03 and i want to make from field: "AUDIT_TIME" a filter and on the base of this filter i want to query the new recors.

i know, i can solve this issue, if i reload all records and use the resident load, but i want to know whether is that possible to query the records by first load.

thanks a lot

Beck

7 Replies
omarbensalem
Esteemed Contributor

Re: data query (with timestamp)

u can achieve this by using where clause when u're loading ur data:

I mean sthing like :

Main_LOHN_LPE:

LOAD "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME"#

     "AUDIT_TIME";

SQL SELECT "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME",

      "AUDIT_TIME"

FROM "LOHN_LPE"

WHERE add ur condition here (what u want to load from ur db)

;

beck110979
Valued Contributor III

Re: data query (with timestamp)

Hi Omar,

thanks a lot for your feedback, i tried it but it does not work

my procedure does look like:

timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss') as "AUDIT_TIME$",

if(floor(timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss'))=today(),'Today',

if(floor(timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss'))=today()-1,'Yesterday','old data')) As DayFilter

but i dont know how to handle this in where condition, i tried but i didnt achieve any results

Beck

big_dreams
Contributor III

Re: data query (with timestamp)

You can also put where clause in preceding load;

Like

Main_LOHN_LPE:

LOAD "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME"#

     "AUDIT_TIME"

WHERE add ur condition here (what u want to load from ur db);

SQL SELECT "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME",

      "AUDIT_TIME"

FROM "LOHN_LPE"

;

big_dreams
Contributor III

Re: data query (with timestamp)

on which field you want to put filter???

try below

Main_LOHN_LPE:

LOAD "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME"#

timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss') as "AUDIT_TIME$",

if(floor(timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss'))=today(),'Today',

if(floor(timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss'))=today()-1,'Yesterday','old data')) As DayFilter

WHERE timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss')='08.02.2017 08:15:03';

SQL SELECT "EMPL_NR",

    "LAST_NAME",

    "FIRST_NAME",

      "AUDIT_TIME"

FROM "LOHN_LPE"

;

Regards

beck110979
Valued Contributor III

Re: data query (with timestamp)

Hi Max,

thanks a lot for your feedback, but it does not work

big_dreams
Contributor III

Re: data query (with timestamp)

Can you explain little bit more on it does not work?


Is it throwing any error??


or Only loading data for that specific date??


Regards,

beck110979
Valued Contributor III

Re: data query (with timestamp)

Hi Max,

i used this procedure, and it seems to be working:

where(floor(timestamp("AUDIT_TIME$",'DD.MM.YYYY hh:mm:ss'))=today()-1);

SQL SELECT *

FROM "LOHN_LPE";

but i am not sure. if you have any ideas. please let me know