Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

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

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

Hi DataNibbler,

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

- Marcus

10 Replies

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

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

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

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
Esteemed Contributor

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

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

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

Hi DataNibbler,

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

- Marcus

datanibbler
Esteemed Contributor

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

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
Esteemed Contributor

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

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.

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

Hi DataNibbler,

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

- Marcus

datanibbler
Esteemed Contributor

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

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

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

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

Community Browser