Get Information of Top N from a CSV File

 

Problem description & analysis

In the following CSV file, the first column is id, the second column is roomname, the third column is date, the fourth column is time, the fifth column is LOB (length of booking), and the sixth column is PB (person booking), as shown below:

id,roomname,date,time,LOB,PB

1,Gower,2000-01-01,0:00:00,281,Jack

2,Usk,2000-01-01,0:00:00,291,Jack

3,Wye,2000-01-01,0:00:00,283,Jack

4,Bala,2000-01-01,0:00:00,282,Jack

5,Pen y Fan,2000-01-01,0:00:00,292,Jack

6,Llangorse,2000-01-01,0:00:00,290,Jack

7,Snowdon,2000-01-01,0:00:00,288,Jack

8,Taff,2000-01-01,0:00:00,296,Jack

9,Cadair Idris,2000-01-01,0:00:00,292,Jack

We are trying to find n rooms that will be reserved for the longest time, and their specific lengths of booking. Suppose n is 5, we have the following desired result:

1: Taff 296

2: Cadair Idris 292

3: Pen y Fan 292

4: Usk 291

5: Llangorse 290

Solution

We write the following script p1.dfx in esProc:

A

1

=file("csv.csv").import@tc(roomname,LOB)

2

=A1.top(n;-LOB)

3

=if(n==1,output("RoomName     :"/A2.roomname/""/A2.LOB),output(A2.(#/":     "/roomname/" "/LOB).concat@n()))

Explanation:

Set cellset parameter n.

A1   Import roomname column and LOB column from the CSV file.

A2  Get records containing the first n maximum LOBs (length of booking). n is the script parameter.

A3  If n is 1, output the room name with the longest LOB and the specific length; otherwise output the n room names with the longest LOB and their specific lengths.

Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/63645363/read-csv-file-most-booked-rooms