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: 
beck_bakytbek
Master
Master

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

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)

;

beck_bakytbek
Master
Master
Author

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
Creator III
Creator III

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
Creator III
Creator III

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

beck_bakytbek
Master
Master
Author

Hi Max,

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

big_dreams
Creator III
Creator III

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,

beck_bakytbek
Master
Master
Author

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