Query Sequences to Populate Transposed Values to Another Worksheet

 

Problem description & analysis

The sheet2 of an Excel file is a to-be-searched data area, where column A contains start dates and column B contains end dates, and the date periods of each row are not overlapped. Below is part of the data area:


A

B

C

D

E

F

1

this is a   title

2

date   period

2wk HH

WPH

WCP

WPL

3

2018/7/10

2018/7/14

0.7839

0.7729

0.772

0.7655

4

2018/7/17

2018/7/21

0.799

0.794

0.7909

0.7599

5

2018/7/24

2018/7/28

0.8069

0.8007

0.7911

0.7783

By entering a date parameter, we get the corresponding row data, take columns from C to F, transpose the row data to column data, and populate values in C2:C5 of sheet1. Suppose the date parameter we entered is 2018/7/18, then sheet 1 will become as follows:


A

B

C

1


result   below

2


2wk HH

0.799

3


WPH

0.794

4


WCP

0.7909

5


WPL

0.7599

The algorithm involves sequence query and single-record transposition.

Solution & explanation

Copy data from the 3rd row in Excel sheet2 downwards and paste it to cell A1 in the esProc script. Then we write the following code:


A

1

(Data   copied from Excel)

2

=A1.split@np("\t")

3

=A2.select@1(pDate>~(1)   && pDate<=~(2))

4

=A3.to(3,)

A2: Split and parse A1s string as a sequence of sequences. The @p option means to parse the data to an appropriate type automatically, which is date here. 

A3: Query sequences by a certain date period. Here pDate is a cellset parameter, whose value is, e.g. 2018/7/18. The date format should be consistent with that defined in Excel, which is yyyy/mm/dd.

A4: Get members of A3s sequence from the 3rd one on.

After the above script is executed, copy and paste A4s result to cell C2 in Excel sheet1 to complete the computation.

Q & A Collection

https://stackoverflow.com/questions/63840410/lookup-data-based-on-a-date-that-falls-between-two-dates