Assume, I have created a table to record electricity meter readings of different consumers randomly.
with columns named as
[Consumer Name]
[Consumer ID]
[Meter Reading] and
[Date])
And suppose, if I have recorded data of 10 consumers as below:
(1) 8 consumres = 8 records(single record for each consumer)
(2) 2 consumers = 4 records ( two records for each consumers)
Now, there will be total 12 record rows, in which for two consumers there are double entries.
My question is to create a slice, which shows only 10 entries(one for each, which fullfill slice condition) out of 12 records in the original table.
Is it possible? Plz help
Yes, your expression could be like this:
IN([ID], TOP(SELECT(Readings[ID], [Consumer] = [_THISROW].[Consumer]), 1))
Basically, get the list of rows for this consumer and pick the top row and return only the ID that matches that top row.
3 Likes
Hi WillowMobileSys
Thank u for your valuable reply. Here I need to know little more on this.
As u told:
“Basically, get the list of rows for this consumer and pick the top row and return only the ID that matches that top row.”
Rather than picking up top row, can we put some customize expression to select the row to display .
For example : I want to display latest row(which row has highest value of column[Date]
Yes, as @SkrOYC mentions its basically the same EXCEPT you simply need to order the returned rows so that the latest is that TOP 1.
For this you will want to use the ORDERBY() function to re-order the rows returned from the SELECT() in DESCENDING order by Date for the TOP() function. Something like this:
IN([ID], TOP(ORDERBY(SELECT(Readings[ID], [Consumer] = [_THISROW].[Consumer]), 1), [YourDate], TRUE))
The TRUE indicator at the end tells the ORDERBY to sort descending order.
You can read more about the ORDERBY here:
https://help.appsheet.com/en/articles/2357312-orderby
3 Likes
Hlo WillowMobileSys
Thanks for your support, it works with some correction at end of expression suggested by you.
IN([ID], TOP(ORDERBY(SELECT(Readings[ID], [Consumer] = [_THISROW].[Consumer]), 1), [YourDate], TRUE))
As:
TOP(ORDERBY(SELECT(Readings[ID], [Consumer] = [_THISROW].[Consumer]),[YourDate],TRUE),1))
Is it ok?
1 Like