Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Have you tried what I suggested above?
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..
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.
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
How many sensors are sending data???
Can we store sensor wise minimum timestamp?
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.
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
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
Please Note i can not able to create Variables because i have to build this in qliksense.
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