topic Re: Getting Initial and Final values in a record in QlikView

Getting Initial and Final values in a record

shyamcharan — Mon, 23 Jan 2017 02:04:52 GMT

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

Re: Getting Initial and Final values in a record

sunny_talwar — Mon, 23 Jan 2017 02:11:55 GMT

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];

Re: Getting Initial and Final values in a record

sunny_talwar — Mon, 23 Jan 2017 02:14:09 GMT

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

1) FirstSortedValue(BP, [Display sequence])

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

Re: Getting Initial and Final values in a record

shyamcharan — Mon, 23 Jan 2017 03:32:24 GMT

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.

Re: Getting Initial and Final values in a record

sunny_talwar — Mon, 23 Jan 2017 10:47:12 GMT

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.

Re: Getting Initial and Final values in a record

shyamcharan — Mon, 23 Jan 2017 22:17:17 GMT

I agree Thanks again.