Query Sequences to Populate Transposed Values to Another Worksheet
Problem description & analysis
In an Excel file, sheet2 contains a tobesearched 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 singlerecord transposition.
Solution & analysis
Copy data in Excel sheet2 from the 3^{rd} 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 3^{rd} one.
After the above script is executed, copy and paste A4’s result to cell C2 in Excel sheet1 to complete the computation.