topic Re: Search condition for date in a timestamp field in App Development

Search condition for date in a timestamp field

jananireddy1 — Sat, 16 Nov 2024 17:23:41 GMT

Hi Fellow Qlikers,

I am trying to search a column of a table. The field coming from db is a timestamp field.

Is there a way i can ignore the timestamp and just be able to write my search condition as >=02/20/2020 and it still fetches me results

Re: Search condition for date in a timestamp field

Or — Fri, 12 Mar 2021 20:21:37 GMT

I attempted to replicate this on my end, but I'm getting the correct values returned when I search like you described (albeit with a different timestamp format):

It looks like your values are correct timestamps, so I wonder if this might be related to the formatting somehow...

Re: Search condition for date in a timestamp field

jananireddy1 — Mon, 22 Mar 2021 13:31:03 GMT

Hi, Any pointers as to how to correct the formatting

In the data load editor I have the standard SET dateformat and timestampformat commands

Re: Search condition for date in a timestamp field

Or — Mon, 22 Mar 2021 13:43:44 GMT

Nothing specific - if you could provide sample data, perhaps the issue will be visible, but working blindly I have no recommendation except to explicitly format the field in question as e.g. Timestamp(Field) or Timestamp#(Field) as appropriate.

Also, if you don't need the timestamp aspect at all, it'd probably be better to just filter on a date field, e.g. dayname(Field).

Re: Search condition for date in a timestamp field

jananireddy1 — Mon, 22 Mar 2021 13:58:57 GMT

Sure let me work on getting you a sample app.

Re: Search condition for date in a timestamp field

jananireddy1 — Tue, 23 Mar 2021 19:53:55 GMT

Hi, I have attached a sample app to replicate my issue

Thankyou

Re: Search condition for date in a timestamp field

Or — Mon, 05 Apr 2021 22:12:11 GMT

Well, sorry I'm no help, but I'm officially confounded here.

Load Timestamp(Now(),'DD/MM/YYYY hh:mm:ss') as TestTime
Autogenerate 1;

Load Timestamp#(TimeStamp,'DD/MM/YYYY hh:mm:ss') as TimeStamp INLINE [
TimeStamp
03/03/2020 11:11:11
04/04/2020 5:55:55
01/01/2020 3:33:33 ];

Both fields get picked up as $numeric and $timestamp, but TestTime won't let me search e.g. >01/01/2020 whereas TimeStamp will let me search e.g. >01/01/2020.

Either I'm missing something (likely) or this is some sort of bug / unintended behavior / something of that nature (less likely), but I'm fresh out of ideas as to what I might be missing.

Re: Search condition for date in a timestamp field

jananireddy1 — Mon, 05 Apr 2021 20:28:55 GMT

No Problem, i appreciate your help so far on this. Let me try to do some more research on my end .. may be it has to do with the format.

Thankyou!

Re: Search condition for date in a timestamp field

eddie_wagt — Tue, 06 Apr 2021 09:17:58 GMT

Maybe if you make it a dual.

Like this

=dual(Timestamp(created_date),date(floor(created_date)))

Regards Eddie

Re: Search condition for date in a timestamp field

jananireddy1 — Tue, 06 Apr 2021 18:25:13 GMT

HI @eddie_wagt with the dual function it definitely helped be search. So that's what i wanted, But this formula is eating up a part of my data.

If you look at the image the dates highlighted in yellow does not show when we use dual function. What i feel its doing is showing just one timestamp entry for March 2nd

Thankyou

Re: Search condition for date in a timestamp field

eddie_wagt — Wed, 07 Apr 2021 06:30:24 GMT

Hello @jananireddy1 ,

Could you check if this expression is better? I cannot test it myself.

=dual(created_date,num(Timestamp(created_date)))

Regards

Eddie

Re: Search condition for date in a timestamp field

jananireddy1 — Thu, 08 Apr 2021 15:39:44 GMT

Perfect! I think this works, i am testing out a few more use cases with this. Meanwhile i am hoping you could explain this to me in a few words? Wondering why the timestamp format does not work as it is

Re: Search condition for date in a timestamp field

eddie_wagt — Fri, 09 Apr 2021 08:42:41 GMT

Great. Hopefully it works for you. I am guessing here, but I think the timestamp is not being interpreted as a integer/number. With the expression dual you can combine a string (what you want to see) with a number (what has to be caculated).

Maybe if you try to solve it in loadscript, you can load the timestamp like this.

Time( TimeField, 'hh:mm:ss TT')

Maybe this is a solution? Again I cannot see the source so maybe the formula has to be tweakerd.

Regards Eddie

Re: Search condition for date in a timestamp field

jananireddy1 — Fri, 09 Apr 2021 14:05:57 GMT

Sounds good! i can try playing around in the load script as well. But for now your solution works perfectly good.

Thankyou