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: 
Anonymous
Not applicable

How to compress data in timeline using variable e.g. 0-10 minutes?

Hi,

My log file has about 720000 rows per day, so I need to give user opportunity to average/count data over time e.g. 1/5/10 minutes average or 3/5/7 minutes count. Selections can be predefined integers or slider between min and max.

But I don't have an idea how or where this procedure would be best to do, in data loading script (e.g. something similar like master calendar but instead of date to do it for time) or in visualization phase or somewhere else?

Data has four fields: Timestamp, TagID, Reader, RSSI.

1) In first sheet I would like to average RSSI values over time and then update line chart respectively for e.g. 5 minutes average.

TagID timeline.png

2) In second sheet I would like to count Readers over time then update bar chart respectively for e.g. 5 minutes average.

Reader count.png

I would appreciate any help or tips or links to reference material. Thanks in advance.

Marko

5 Replies
shraddha_g
Partner - Master III
Partner - Master III

If you have created any sample app then provide sample app with sample data to test.

Anonymous
Not applicable
Author

Hi,

Here is sample app and data for tests. Please note that data has only 18 seconds so average can be adjusted from minutes to seconds.

Marko

marcus_sommer

I suggest using a master-calendar and a master timetable by splitting the timestamp into a date and a time:

date(floor(Timestamp)) as Date,

time(frac(Timestamp)) as Time

The Master Time Table

How to use - Master-Calendar and Date-Values

- Marcus

Anonymous
Not applicable
Author

Hi Marcus,

Thanks for the tip. It took a while to figure it out but those links helped.

Now I do a static one minute interval in data load script and using that as chart x-axis.

Data load script:

[viasec-server-log]:

LOAD

    [@1] & ' ' & [@2] AS Timestamp,

    date(floor([@1])) AS Date,

    time(floor(time#([@2],'hh:mm:ss,fff'),1/24/60),'hh:mm') AS Time, //one minute interval

    [@4] AS Reader,

    [@9] AS Keyword,

    [@12] AS TagID,

    [@14] AS RSSI

FROM [lib://qlikid_markoseppala/viasec-server_test2.log]

(txt, codepage is 28591, no labels, delimiter is spaces, msq)

WHERE [@9]='com.viasec.module.syris.DefaultSyrisEngine';

1) Line chart

TagID 2.png

2) Bar chart

Reader 2.png

In similar manner I could do other static intervals e.g. 1/3/5/10 minutes

time(floor(time#([@2],'hh:mm:ss,fff'),1/24/60),'hh:mm') AS Time_1m, //one minute interval

time(floor(time#([@2],'hh:mm:ss,fff'),1/24/20),'hh:mm') AS Time_3m, //three minute interval

time(floor(time#([@2],'hh:mm:ss,fff'),1/24/12),'hh:mm') AS Time_5m, //five minute interval

time(floor(time#([@2],'hh:mm:ss,fff'),1/24/6),'hh:mm') AS Time_10m, //ten minute interval

Can these different time intervals integrated to same chart easily and give user possibility to switch between dimensions?

Marko

marcus_sommer

You could use a cyclic dimension-group to give the user the possibility to choose various dimensions within the same object. I don't know if this feature is already natively implemented within the last release - if not it could be emulated:

Emulating cyclic dimension group in Qlik Sense Desktop

Makes Sense to go round in circles

- Marcus