RaqForum 28 No.
1 Reply  •  310 View  • 
     ...

How to Add Missing Values in a Continuous Sequence

 
excel(115) align(2) join(4)

Here’s Excel file book1.xlsx:

sadf

ugbinvma

26

2020-01-01

sadf

ugbinvma

37

2020-03-01

sadf

ugbinvma

22

2020-09-01

sadf

ugbinvma

87

2020-11-01

We need to add the missing dates in column 4 to get the following table:

sadf

ugbinvma

26

2020-01-01

2020-02-01

sadf

ugbinvma

37

2020-03-01

2020-04-01

2020-05-01

2020-06-01

2020-07-01

2020-08-01

sadf

ugbinvma

22

2020-09-01

2020-10-01

sadf

ugbinvma

87

2020-11-01

2020-12-01

。It’s convenient to do this in esProc.
Download esProc installation package and free DSK edition license HERE.

Write script p1.dfx in esProc:

A

1

=file("book1.xlsx").xlsimport@w()

2

=periods@m(date("2020-01-01"),date("2020-12-01"),1)

3

=A1.align(A2,~(4))

4

=A3.run(if(~==null,~=[,,,A2(#)]))

5

=file("book2.xlsx").xlsexport@w(A4)

A1   Import data of book1.xlsx; @w option enables reading data as a sequence of sequences.

A2   Get the complete sequence of dates according to the largest date and the smallest date.

A3   Align A1’s table sequence to A2’s sequence of dates.

A4   Populate dates into empty rows.

A5   Export result to book2.xlsx.

2.     Execute the script to get the expected result.


excel(115) align(2) join(4)
 
 •  310 View  •