Assigning Rank in Load Script

[#1]

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

[jagan]

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.

[#3]

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

[#4]

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

[jagan · Accepted solution]

Sort the data by date_id and phy_id. This works.