23 Replies Latest reply: Sep 28, 2015 5:13 AM by abhay singh RSS

    Logic Help

    abhay singh

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


        • Re: Logic Help
          balraj ahlawat

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

            • Re: Logic Help
              abhay singh

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

                  • Re: Logic Help
                    balraj ahlawat

                    How many sensors are sending data???

                     

                    Can we store sensor wise minimum timestamp?

                    • Re: Logic Help
                      Stefan Wühl

                      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.

                  • Re: Logic Help
                    abhay singh

                    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