topic Re: Filtering using Set Analysis in App Development

Filtering using Set Analysis

bean — Fri, 27 Jan 2023 09:20:23 GMT

Hi all,

I've a master dimension expression formulated which returns those entries which are in a range of daily time (e.g. a log file).

The expression looks as follows and does not really perform (as expected, but it works ;-)). The desired time range is given by two variables from a dropdown list. Null values are then excluded on the sheet's table.

If(
Timestamp#(Timestamp([TIME.payload_Time], 'hh:mm'), 'hh:mm') >= Timestamp#('$(vStartTime)','hh:mm:ss.fff')
and
Timestamp#(Timestamp([TIME.payload_Time], 'hh:mm'), 'hh:mm') <= Timestamp#('$(vEndTime)', 'hh:mm:ss.fff'),
Timestamp([TIME.payload_Timestamp_formatted],'DD.MM.YYYY hh:mm:ss.fff'),
Null()
)

In order to enhance filtering performance I tried it with a Set Analysis statement on a separate field in the table of the sheet. Whatever I do, the set always has Null entries.

count({
[TIME.payload_Time] = {">= Timestamp#('$(vStartTime)','hh:mm:ss.fff')"},
[TIME.payload_Time] = {"<= Timestamp#('$(vEndTime)','hh:mm:ss.fff')"}
>} [TIME.payload_Time])

What goes wrong? Is there a way to debug the the Set Analysis statement? Or is there a better way to filter my table in the sheet?

In the expression editor I can see that the variables resolve correctly:

count({<[TIME.payload_Time] = {">= Timestamp#('00:00.00.000','hh:mm:ss.fff')"},
TIME.payload_Time] = {"<= Timestamp#('12:00:00.000','hh:mm:ss.fff')"}
>} [TIME.payload_Time])

Thanks in advance for your contribution.

Re: Filtering using Set Analysis

luciancotea — Fri, 27 Jan 2023 09:33:37 GMT

In general, for performance reasons, it's better to split one time field into three fields: hour/minute/second

About your null results, an expression can be debugged only with the data that is supposed to operate on. You should add an example.

Re: Filtering using Set Analysis

bean — Fri, 27 Jan 2023 10:05:52 GMT

Sounds a great idea to split the time field into single integers while loading the app. Can imagine that that helps.

Data looks as follows:

[TIME]:
Load * Inline [
TIME.payload_Timestamp, TIME.payload_Time, TIME.payload_Type, TIME.payload_Msg
26.01.2023 08:59:49.667, 08:59:49.667, Info, BlaBla
26.01.2023 08:59:47.664, 08:59:47.664, Info, xxxx
26.01.2023 08:59:47.160, 08:59:47.160, Info, yyy
...
26.01.2023 07:43:26.672 , 07:43:26.672, Info, zzzz
26.01.2023 07:37:06.391, 07:37:06.391, Info, vvvvv
26.01.2023 06:49:45.715, 06:49:45.715, Info, wwww
];
I've set the variables to '06:00:00.000' and '08:00:00.000' respectively.

The count as a measurement column in the sheet's table always returns 0.

Re: Filtering using Set Analysis

luciancotea — Fri, 27 Jan 2023 10:52:40 GMT

Try this:

=count({< TIME.payload_Time = {">=$(Time#(vStartTime,'hh:mm:ss.fff'))<=$(Time#(vEndTime,'hh:mm:ss.fff'))"}>} [TIME.payload_Time])

Re: Filtering using Set Analysis

bean — Fri, 27 Jan 2023 12:52:07 GMT

Thanks so far ... but the count is still zero per line. I would expect a 1 when the row fulfills the filtercriteria.

Further I tried the following with the same result:

=count({<[TIME.payload_Time] = {">=Time#('$(vStartTime)','hh:mm:ss.fff')<=Time#('$(vEndTime)','hh:mm:ss.fff')"}>} [TIME.payload_Time])

Re: Filtering using Set Analysis

luciancotea — Fri, 27 Jan 2023 13:01:08 GMT

Then this:

=count({< TIME.payload_Time = {">=$(=Time#(vStartTime,'hh:mm:ss.fff'))<=$(=Time#(vEndTime,'hh:mm:ss.fff'))"}>} [TIME.payload_Time])

Re: Filtering using Set Analysis

bean — Fri, 27 Jan 2023 15:15:15 GMT

Thanks ... quite comprehensive synthax 😉
Unfortunately still get the same result. The count still sees zero.

However the expression editor resolves the where clause as follows, which looks o.k. to me:
=count({<[TIME.payload_Time] = {">='06:00:00.000'<='09:00:00.000'"}>} [TIME.payload_Time])

So I entered directly the following statement as expression to simplify the problem:
=count({<[TIME.payload_Time] = {">='06:00:00.000'"}>} [TIME.payload_Time])
and updated the load script to ensure having a time in the second column.

SET TimeFormat='hh:mm:ss[.fff]';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

[TMP]:
LOAD * Inline [
payload_Timestamp, payload_Time, payload_Type, payload_Msg
26.01.2023 08:59:49.667, 08:59:49.667, Info, BlaBla
26.01.2023 08:59:47.664, 08:59:47.664, Info, xxxx
26.01.2023 08:59:47.160, 08:59:47.160, Info, yyy
26.01.2023 07:43:26.672, 07:43:26.672, Info, zzzz
26.01.2023 07:37:06.391, 07:37:06.391, Info, vvvvv
26.01.2023 06:49:45.715, 06:49:45.715, Info, wwww
];

[TIME]:
LOAD
Timestamp#(payload_Timestamp,'DD.MM.YYYY hh:mm:ss.fff') AS TIME.payload_Timestamp,
Time#(payload_Time,'hh:mm:ss.fff') AS TIME.payload_Time,
payload_Type AS TIME.payload_Type,
payload_Msg AS TIME.payload_Msg
RESIDENT TMP;

To me it looks like the comparison operator has some trouble with Time(). There are similar solutions as you told me using Timestamp().