topic Re: Scenario Help in QlikView

Scenario Help

abhaysingh — Fri, 02 Oct 2015 17:58:00 GMT

Hey guys ,

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

I have one table

Load

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

Re: Scenario Help

pamaxeed — Fri, 02 Oct 2015 18:02:33 GMT

Hi in the script do something like that:

Output::

Load

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

Re: Scenario Help

abhaysingh — Fri, 02 Oct 2015 18:05:27 GMT

Output::

Load

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

Re: Scenario Help

pamaxeed — Fri, 02 Oct 2015 18:07:41 GMT

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

Thanks

Re: Scenario Help

pamaxeed — Fri, 02 Oct 2015 18:11:32 GMT

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

Re: Scenario Help

abhaysingh — Fri, 02 Oct 2015 18:50:58 GMT

HI Patric..

Do i require to aggregate Temp as well??

Max(Temp) as MaxTemp

pls advice

Re: Scenario Help

pamaxeed — Fri, 02 Oct 2015 19:24:09 GMT

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

Re: Scenario Help

MarcoWedel — Fri, 02 Oct 2015 23:25:29 GMT

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