Getting Initial and Final values in a record

[shyamcharan]

Hi Experts,

I have data as below

ID	Display sequence	BP
1	4	107
1	2	108
1	3	109
1	1	110
2	6	110
2	2	120
2	3	122
2	5	123

My requirement is to get the Initial and Final Blood Pressure readings as below:

ID	BP Initial	BP Final
1	110	107
2	120	110

Here the Display sequence is the number that tells the sequence of the reading and it does not always start at 1 in the data.

The result should find the minimum number in the display sequence against each ID and then get the BP value as BP Initial

The result should find the Maximum number in the display sequence against each ID and then get the BP value as BP Final

Thanks in advance

Regards,

Shyam

[sunny_talwar · Accepted solution]

You need this in the script? Try this

[BP monitoring]:

LOAD * INLINE [

    ID, BP, Display sequence

    1, 110, 1

    1, 108, 2

    1, 109, 3

    1, 107, 4

    2, 120, 2

    2, 122, 3

    2, 123, 5

    2, 110, 6

];

FinalTable:

LOAD ID,

  FirstSortedValue(BP, [Display sequence]) as [BP Initial],

  FirstSortedValue(BP, -[Display sequence]) as [BP Final]

Resident [BP monitoring]

Group By ID;

DROP Table [BP monitoring];

[sunny_talwar]

Or you can do this on the front using similar FirstSortedValue Functions

1) FirstSortedValue(BP, [Display sequence])

2) FirstSortedValue(BP, -[Display sequence])

[shyamcharan]

Wow, thanks heaps Sunny.

I think I will go with the first suggested solution and do it in the Database to have a separate table as I would need the other readings too in the dashboard.

When I use the second solution, every time I open the document it is taking a lot of time to display the dashboard.

I believe this will delay the display of the dashboard on the Access point

Please advice if I am wrong.

Regards,

Shyam.

[sunny_talwar]

performing something is background is always faster than doing it on the front end. But if you plan to perform 15 different things or if you want a calculation to change based on selection, then doing it in the back end might not make sense. So I guess knowing the trade-offs you can decide doing one or the other.

[shyamcharan]

I agree Thanks again.