Scenario Help

[abhaysingh]

Hey guys ,

I have one simple scenario, Need some advice in this.

I have one table

Load

ID

SensorID

Temprature

Date

From Source..

Sensor Sending Data Every Minute

i.e.SensorID     TempIn Degree Celcius     Date

     Sensor1           -2                                 10/02/2015 11:22:33 AM                   

     Sensor1           -4                                 10/02/2015 11:23:33 AM               

     Sensor1           -5                                 10/02/2015 11:24:33 AM               

     Sensor2           0                                 10/03/2015 13:22:33 PM                   

     Sensor2           -3                                 10/03/2015 13:23:33 PM               

     Sensor2           -8                                10/03/2015 13:24:33 PM

Output Required

SensorID	Current Temp( Last REcord of Temprature)	Date
Sensor1	-5	10/02/2015 11:24:33 AM
Sensor2	8	10/03/2015 13:24:33 PM

and so on ,, also it should work on Calender like Year, Month, Date,week,Day.

Please Advice..

thanks

[pamaxeed]

Hi in the script do something like that:

Output::

Load

ID

SensorID

Temprature

Date

From Dummy;

Inner Join

Load Max(Date) as Date,

         SensorID

Resident Source

Group By SensorID;   EDIT: Thanks to Abhay I was to fast in posting and forgot it

Only the Last Temperature Records are loaded in this example referred to the SensorID.

Cheers,

Patric

[abhaysingh]

Output::

Load

          ID

     SensorID

     Temprature

     Date

From Dummy;

Inner Join

MaxTemp:

Load Max(Date) as Date,

     Max(Temprature) as MaxTemp,               // Does it required?? and Group by must require with aggr function right?

         SensorID

Resident Source group by SensorID;

thanks

[pamaxeed]

Yes you are right the group by is necessar was to fast in writing and forgot the IMPORTANT Group By Clause

Thanks

[pamaxeed]

On other hand you can just load the source table as it is and try to use an expression on the presentation layer GUI in your output table, like this:

Sum(aggr(firstsortedvalue(Temperature,-Date),SensorID)

Hope it helps you

[abhaysingh]

HI Patric..

Do i require to aggregate Temp as well??

Max(Temp) as MaxTemp

pls advice

[pamaxeed]

If you mean in the script NO because what we need is the temperature of the last date time by SensorID:

So my code should work, i join the max date by sensorID with the original table and obtain the desired output table....

[MarcoWedel · Accepted solution]

Hi,

as you would like it to work on Calender like Year, Month, Date,week,Day as well, you could also perform the aggregation in the front end:

hope this helps

regards

Marco