I want to create an EXPRESSION ! Do help

[#1]

Hello All,

The query here is I have some device which are frequently upgrading and after update they turn to again updating. For In progress the response code is 'NA' and for updated it is '0'.

I have one column with me which shows the date-time latest now I want to pick the

latest response code of a particular device id whether it would be '0' or 'NA' on basis of "com_send_time"  Column .

Scenario is lik this

P.S: I have around 700 device !

device_id	response_code	com_send_time
1852	NA	23-07-2015 13:28
1852	NA	24-07-2015 11:42
1852	0	23-07-2015 13:41
1852	0	24-07-2015 11:54
1852	-	-
2314	NA	24-07-2015 11:41
2314	0	24-07-2015 11:59
2314	-	-

As you can see above 1852 is showing different response code but I just want the count for the latest

i.e  24-07-2015 11:54

[antoniotiman]

Hi Cyrus,

Try this script

Temp:
LOAD device_id, response_code,
TimeStamp(TimeStamp#(com_send_time,'DD-MM-YYYY hh:mm')) as com_send_time 
Inline [
device_id, response_code,com_send_time
1852, NA, 23-07-2015 13:28
1852, NA, 24-07-2015 11:42
1852, 0, 23-07-2015 13:41
1852, 0, 24-07-2015 11:54
1852, -, -
2314, NA, 24-07-2015 11:41
2314, 0, 24-07-2015 11:59
2314, -,
-
];
  Inner Join LOAD
device_id,
Max(com_send_time) as com_send_time
Resident Temp
Group By device_id;

[#3]

hello antonio,

I have many device_id bro like 700 how can I mention all in Inline

cant I use device ID column directly.

[antoniotiman]

I'm sorry,

but i didn't understant.

What is your source data ?

[#5]

Its on server so U cannot define the date I get automatically update on device response and there are around 24 tables interlinked so device total is 700 they all get frequent update so I jus want to see the latest response code of a particular device id which i will select.

[#6]

I want an expression guys..!! Not a Script!!

jus an normal expression to view the latest RESPONSE CODE on basis of com_send_time and show that  DEVICE ID

thats it !

[settu_periasamy]

Hi,

Try this

=FirstSortedValue(response_code,-com_send_time)

[#8]

It is showing blank space !!

like

-

[settu_periasamy]

may be like the attachment.

[#10]

I want to have the count of that device id with response code 0 or NA

the expression u gave me is returning a text