topic Re: Assigning Rank in Load Script in QlikView

Assigning Rank in Load Script

Tue, 19 Nov 2013 06:07:24 GMT

Hi All,

I have data in this format.

DATE_ID	PATIENT_ID	PHY_ID	Value
1	1	1	26
1	11	1	25
2	1	1	18
2	11	1	21
3	15	1	14
3	1	1	22
3	5	1	21
3	7	1	18
3	11	1	22
4	1	1	25
4	11	1	15
4	12	1	7
4	15	1	14
4	20	1	21

I need to assign rank in on basis of this logic. In a paticular Date Id , if a phy gets 4 patients then Patients are assigned 1,2,3,4.

something like this,

DATE_ID	PATIENT_ID	PHY_ID	Value	Patient_Rank
1	1	1	26	1
1	11	1	25	2
2	1	1	18	1
2	11	1	21	2
3	15	1	14	3
3	1	1	22	1
3	5	1	21	2
3	7	1	18	3
3	11	1	22	4
4	1	1	25	1
4	11	1	15	2
4	12	1	7	3
4	15	1	14	4
4	20	1	21	5

Kindly let me know how to achieve this.

Thanks

Gauraw

Re: Assigning Rank in Load Script

jagan — Tue, 19 Nov 2013 07:01:39 GMT

Hi Gaurav,

Please try below script

Test:

LOAD
*,
If(Previous(DATE_ID) <> DATE_ID OR Previous(PHY_ID) <> PHY_ID, 1, Peek('Rank') + 1) AS Rank
INLINE [
DATE_ID, PATIENT_ID, PHY_ID, Value
1, 1, 1, 26
1, 11, 1, 25
2, 1, 1, 18
2, 11, 1, 21
3, 15, 1, 14
3, 1, 1, 22
3, 5, 1, 21
3, 7, 1, 18
3, 11, 1, 22
4, 1, 1, 25
4, 11, 1, 15
4, 12, 1, 7
4, 15, 1, 14
4, 20, 1, 21
];

Hope this helps you.

Regards,

Jagan.

Re: Assigning Rank in Load Script

Tue, 19 Nov 2013 11:51:49 GMT

Thanx for reply Jagan

I have data like this but the logic suggested by you is not working for below data.kindly check.

DATE_ID	PATIENT_ID	PHY_ID	Value
1	1	1	26
1	2	2	18
1	3	3	21
1	4	4	20
1	5	5	14
1	6	6	19
1	7	7	23
1	8	8	24
1	9	9	27
1	10	10	28
1	11	1	25
1	12	2	22
1	13	3	17
1	14	4	21
1	15	5	17
1	16	6	24
1	17	7	28
1	18	8	27
1	19	9	20
1	20	10	21
2	1	1	18
2	2	2	16
2	3	3	15
2	4	4	25
2	5	5	21
2	6	6	24
2	7	7	19
2	8	8	24
2	9	9	23
2	10	10	14
2	11	1	21
2	12	2	16
2	13	3	17
2	14	4	16
2	15	5	23
2	16	6	23
2	17	7	18
2	18	8	16
2	19	9	15
2	20	10	18
3	1	1	22
3	2	2	20
3	3	3	22
3	4	4	25
3	5	5	24
3	6	6	17
3	7	7	14
3	8	8	21
3	9	9	17
3	10	10	24
3	11	1	22
3	12	2	28
3	13	3	22
3	14	4	20
3	15	5	26
3	16	6	14
3	17	7	17
3	18	8	14
3	19	9	23
3	20	10	19
4	1	1	25
4	2	2	22
4	3	3	19
4	4	4	25
4	5	5	22
4	6	6	18
4	7	7	20
4	8	8	26
4	9	9	15
4	10	10	21
4	11	1	15
4	12	2	28
4	13	3	18
4	14	4	18
4	15	5	22
4	16	6	20
4	17	7	20
4	18	8	25
4	19	9	21
4	20	10	19

Thanks

Gauraw

Re: Assigning Rank in Load Script

Tue, 19 Nov 2013 12:09:22 GMT

Hi,

I would load first into a temp table.

After that, I would load this into a definitve table but

1) by sorting the temp table as you wish (for the rank)

2) by adding the rank field using peek() or previous() function, sth like

if (peek ('DATE_ID') <> DATE_ID, 1, peek('MyRank')+1) as MyRank

inside the if, you must test if you restart from 1 or if you add to the previous Rank.

For these functions, you can read further the helpfile.

Fabrice

Re: Assigning Rank in Load Script

jagan — Tue, 19 Nov 2013 12:29:52 GMT

Sort the data by date_id and phy_id. This works.