Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi DataNibbler,
looked 'YourFormat' like 'DD.MM.YY hh:mm:ss' ?
- Marcus
Create a Variable for Yesterday 10am and use it while fetching data.
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
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
Hi DataNibbler,
looked 'YourFormat' like 'DD.MM.YY hh:mm:ss' ?
- Marcus
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
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.
Hi DataNibbler,
I think you need single-quotes around your variable, like: Timestamp < '$(v_yest)'
- Marcus
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
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