Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abhaysingh
Contributor III

Logic Help

Hey Guys,

Need One Help

I have one date field which containing the date with TimeStamp and its get Upated every seconds in Transaction Table.

I have a report in which Date field is there

i.e Asset  |   Sensor     |     DateRecorded     | Value

     A        |     123        |     24-09-2015 12:00:01 AM |      12344

     A        |     123        |     24-09-2015 12:00:02 AM |      12345

     A        |     123        |     24-09-2015 12:00:03 AM |      12346

.

..so on

..

     A        |     123        |     24-09-2015 12:00:30 AM |      12347

     A        |     123        |     24-09-2015  01:00:01 AM |      12348

and so on

updated in transaction Table

I want to Create a  Interval like 10 min, 30 min, 1 hour, 2 hour etc

Now i want when i select any Interval Suppose 10 Min, the Report Should Display like Below

     Asset  |   Sensor     |     DateRecorded               | Value

     A        |     123        |     24-09-2015 12:00:01 AM |      12344

     A        |     123        |     24-09-2015 12:00:11 AM |      12345

     A        |     123        |     24-09-2015 12:00:21 AM |      12346

     A        |     123        |     24-09-2015 12:00:31 AM |      12347


and so on


and Same if i select 30 Min the report should update like below


    Asset  |   Sensor     |     DateRecorded               | Value

     A        |     123        |     24-09-2015 12:00:01 AM |      12344

     A        |     123        |     24-09-2015 12:00:31 AM |      12345

     A        |     123        |     24-09-2015 01:00:01 AM |      12346

     A        |     123        |     24-09-2015 01:00:31 AM |      12348



DateRecorded Field i am getting in Transaction Table


TxnTable:

Load

Field1,

Field2,

DateRecorded


from Source;


Please Suggest how i can achieve it..


1 Solution

Accepted Solutions
MVP
MVP

Re: Logic Help

Have you tried what I suggested above?

23 Replies
balrajahlawat
Esteemed Contributor

Re: Logic Help

Abhay,

I think, the challenge is here to create a interval from DateRecorded. And once you are able to create interval from the mentioned date&Time, with associativity you will easily get what you required.

As QS does not support variable, you can fix a value of time as per your requirement into a calculated measure.

Say, a CalculatedMeasure=12:00:00, then use if condition like

IF(time(datefield)>=CalculatedMeasure & time(datefield)<=12:11:00, '10 min'....

and so on.....

I think you need to hardcode your interval values as per your requirement....

and then use it in any way.

Hope it will help you..

abhaysingh
Contributor III

Re: Logic Help

No balraj i can not fix any value because there would be scenario when sensor sending data at 01:00:00 AM

than i have check the interval as per there Min Record.

Re: Logic Help

Double-Posting isn't helpful: Logic Help1

I suggest you split your timestamp into a date- and a time-field and used a master-calendar and a master-time-table in which you have included your minute-cluster, maybe in this way:

ceil(minute(YourTimeField)) / 10) as MinuteCluster10

And within your objects you put date + time respectively time-Cluster together again. Here you will find many more informations and howto's to these topic: How to use - Master-Calendar and Date-Values

- Marcus

balrajahlawat
Esteemed Contributor

Re: Logic Help

How many sensors are sending data???

Can we store sensor wise minimum timestamp?

MVP
MVP

Re: Logic Help

As far as I understand, I think you need something like this:

1) Load your raw data

2) Find the minimum timestamp per Sensor and Join back

JOIN (RAW)

LOAD    Asset, Sensor,

               Min(DateRecorded) as MinDateRecorded

RESIDENT RAW

GROUP BY Asset, Sensor;

3) Do a resident LOAD of RAW data and create a relative timestamp based on MinDateRecorded:

LOAD *,

          DateRecorded - MinDateRecorded as RelativeTimeStamp

RESIDENT RAW;

DROP TABLE RAW;

Now in your analysis, do something like (in a calculated dimension):

=If( Mod(Minute(RelativeTimeStamp), 10) = 0, DateRecorded)

for your 10 minutes intervals.

abhaysingh
Contributor III

Re: Logic Help

Hi Swuehl,

Thanks for revert, Do i need to follow it for every interval??

like if i have 10 Interval which is 10,15,30,60,90,120,240 etc Min than do i need to do it for every Interval..

Is there any way that i can give Custom Time Selection Input??

Regards

Abhay

abhaysingh
Contributor III

Re: Logic Help

Hi Swuehl,

I am Attaching Some Sample Data with Requirement. Please look into this.

I need Interval as Filter having Values 10 Min, 30 Min, 1 hour, 2 Hour, 5 Hour and Custom input of time if Possible.

and When some one will select 10 min, than report should show with the interval of 10 Min , if 30 min selected than report should show with interval of 30 min ans so on.

Its Like Rather than look into every second update of data, By selecting Interval, data is reduced in Report with selected INterval.

Pls suggest

regards

Abhay

abhaysingh
Contributor III

Re: Logic Help

Please Note i can not able to create Variables because i have to build this in qliksense.

Re: Logic Help

Have you tried my suggestion with master-calendar and master-time-table and creating there cluster-fields for your filters? And you will need multiple flag-fields (for which you could use dimension-groups within the gui).

- Marcus

Community Browser