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

[abhaysingh]

Sorry Not Understood,, could you please provide some sample..

thanks

[marcus_sommer]

It meant to split the timestamp-field into a date- and a time-field:

...

floor(timestamp) as date,

frac(timestamp) as time

...

and to associate those fields with the master-calendar and master-time-table. A howto with various examples will you find here: How to use - Master-Calendar and Date-Values.

- Marcus

[abhaysingh]

pls suggest

[swuehl · Accepted solution]

Have you tried what I suggested above?

[abhaysingh]

Hi Swuehl,

Thanks for revert...

this is the solution i am looking for thanks alot...

But when i am trying it at my end i am getting different output than your...

Like When i m doing

Date- MinDate= RelativeTimeStamp

Than you are getting in hh:mm:ss format, but i m getting i Number format...

is there any extra things which i need to take care of??

Not getting desired output at my end

thanks

abhay

[swuehl]

You may need to format as interval, like

LOAD

     Interval(Date - MinDate)  AS RelativeTimeStamp,

[abhaysingh]

Hi Swuehl,

I have tried the same way u suggested... But not getting the desired output..

Attaching the application i m working on..

Please look into it once...

Pls Suggest.

THANKS

Abhay

[abhaysingh]

Please Find the attached QVD as Well..

thanks

Abhay

[swuehl]

I don't see any issues with the format of the RelativeTimestamp.

When filtering the chart, you may need to check for the second of the RelativeTimestamp, too:

=If( Mod(Minute(RelativeTimeStamp), Interval) = 0 and Second(RelativeTimeStamp)=0, Date)

[abhaysingh]

Hi Swehl,

When i m trying to do sorting in Ascending order, it is not working the way it should be..

Any Suggestion ?

Many Thanks

Abhay