Logic Help

[abhaysingh]

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..

[Anonymous]

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]

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.

[marcus_sommer]

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

[Anonymous]

How many sensors are sending data???

Can we store sensor wise minimum timestamp?

[swuehl]

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]

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]

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]

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

[marcus_sommer]

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