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: 
datanibbler
Champion
Champion

Several filters to put on a LOAD - can I do this?

Hi,

I am not quite sure about this though I know how to do it in principle:

- I need data from a specific database_table, but only

- from a number of specific BINs (I need two separate filters for those because I know some by name, but others are called
   like RECxx (xx being any number), so I need to filter for the first three letters for those and for the whole name for the others)

- I need only the items where the timestamp is "yesterday 10am" or earlier - I have a complete timestamp in the table, but
   going by the way I know (splitting it into a date and a time), I need á rather lengthy filtering_statement for that part.

=> Is there an easier way to filter for "yesterday 10am"?

Thanks a lot!

Best regards,

DataNibbler

P.S.: In other words - can I somehow construct the timestamp "yesterday 10am" to compare the timestamp from the DB data with that?

1 Solution

Accepted Solutions
marcus_sommer

Hi DataNibbler,

looked 'YourFormat' like 'DD.MM.YY hh:mm:ss' ?

- Marcus

View solution in original post

10 Replies
MK_QSL
MVP
MVP

Create a Variable for Yesterday 10am and use it while fetching data.

marcus_sommer

Hi DataNibbler,

I think you will need something like this:

...

where (Field like 'REC*' or Field IN (Value1, Value2 ...)) and YourTimestamp >= $(var);

and var = timestamp((today()-1)+(1/24*10), 'YourFormat')

- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

when I use the statement you have posted for the timestamp, it returns "30.00.15 10:00:00" - pretty close, but it should be "30.11.15 10:00:00". I wonder what I have done wrong. I have tried to copy your statement as precisely as possible.

Best regards,

DataNibbler

marcus_sommer

Hi DataNibbler,

looked 'YourFormat' like 'DD.MM.YY hh:mm:ss' ?

- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

no, it didn't. Now I have it. Thanks!

I have to read up once more on that difference between big and small letters in formats - well, there's a lot I would have to read up on, but there's nothing happening here with regard to QlikView. These days I'm working more with VBA because everyone here is favoring Excel ...

Thanks!

Best regards,

DataNibbler

datanibbler
Champion
Champion
Author

Hi Marcus,

something seems to be going wrong - or I'm doing something wrong, that's more probable.

My timestamp now looks exactly like the timestamps I have in the DB data, format-wise. Still, when I try to build a search_string like >> = Timestamp < $(v_yest) << that doesn't seem to work.

marcus_sommer

Hi DataNibbler,

I think you need single-quotes around your variable, like: Timestamp < '$(v_yest)'

- Marcus

datanibbler
Champion
Champion
Author

Nope, still doesn't work.

I already have a filter in the script for "the day before yesterday or earlier", so that filter, if it worked, should deactivate all values I have in the listbox (if used with a '>' of course) - but it doesn't.

Never mind. I can simply leave the filter I have - nothing can happen before 5am as the early shift doesn't begin until 5:30am.

Thanks anyway!

Best regards,

DataNibbler

marcus_sommer

What happens if you write this manually?

Timestamp < '30.11.15 10:00:00'

Within qv worked this approach if Timestamp conforms to the default-format for a timestamp. But in your case must the queried database handle it - I'm not sure if they is capable to it. Further are you really sure that the format is 'DD.MM.YY hh:mm:ss' and not 'DD.MM.YYYY hh:mm:ss'?

An alternatively could be to use pure numeric values - probably on both sides - like num((today()-1)+(1/24*10)) which avoids the whole formatting-stuff and/or to use a preceeding load and applying the timestamp-where-clause there.

- Marcus