Query Sequences to Populate Transposed Values to Another Worksheet

 

Problem description & analysis

In an Excel file, sheet2 contains a to-be-searched data area, where column A contains start dates and column B contains end dates, and where there are no overlapped date periods between rows. 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. 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 & analysis

Copy data in Excel sheet2 from the 3rd row on 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 A1’string as a sequence of sequences. @p option automatically parses data to an appropriate type, which is date here.

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

A4: Get members of A3’s sequence from the 3rd one.

After the above script is executed, copy and paste A4’s 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