4 Replies Latest reply: Feb 27, 2015 4:43 AM by David Ambrozie RSS

    Generating missing data: multiple values per date

    Simon Brulotte

      Hi,

      there are great tutorials on how to generate a single value per date that are missing. One rate per date is not my case here.

      I have a benchmark that has multiple CUSIPs per date. But my saterdays and sundays aren't populated.

       

      I'm trying to get my script to fill Saterday's list with friday's list, and sunday's with saterday's/friday's.

       

      Here's my scrip so far:

       

       

       

      Benchmark_tmp:

      LOAD *

      , Date  & CUSIP_BENCHMARK as KEY1

      INLINE [

          Date, CUSIP_BENCHMARK

          2015-02-14, A

          2015-02-14, B

          2015-02-14, C

          2015-02-14, D

          2015-02-14, E

          2015-02-14, F

          2015-02-17, A

          2015-02-17, C

          2015-02-17, D

          2015-02-17, E

          2015-02-17, F

          2015-02-17, G

      ]

      ;

       

      MinMaxDate:

      Load Min(Date) as MinDate,

      Max(Date) as MaxDate

      resident Benchmark_tmp;

       

      Let vMinDateIndices = Peek('MinDate',-1,'MinMaxDate') - 1;

      Let vMaxDateIndices = Peek('MaxDate',-1,'MinMaxDate') ;

      Drop Table MinMaxDate;

       

      Join (Benchmark_tmp)

      Load

      Date(recno()+$(vMinDateIndices)) as Date

      Autogenerate vMaxDateIndices - vMinDateIndices;

       

      Benchmark:

      NoConcatenate

      Load Date

      , If( IsNull(KEY1), Peek( KEY1), KEY1 ) as KEY1

      , If( IsNull(CUSIP_BENCHMARK), Peek( CUSIP_BENCHMARK), CUSIP_BENCHMARK ) as CUSIP_BENCHMARK

      Resident Benchmark_tmp

      Order By Date, CUSIP_BENCHMARK ;

      Drop Table Benchmark_tmp;

       

       

      I know the answer is going to lie with the KEY1 component, but I'm just not quite getting there.

      I only get friday's last value for saterday and sunday. I want to get the whole list...

       

      Please help?