Getting previous values for non-existant dates

[ruanhaese]

Hi guys

Hope I'm explaining this clearly but the problem I'm sitting with is that Ive got

(1) Master Calendar Table with ALL days (eg) 01 Jan 2017 to 31 Dec 2017

(2) Share prices for most of the days of the year

the reason theres some missing days is because of weekends and public holidays

So Example:

Calendar:

01 Jan (Sun) - null

02 Jan (M) - null

03 Jan (T) - 10

04 Jan (W) - 11

05 Jan (T) - 12

06 Jan (F) - 13

07 Jan (S) - null

When I'm loading the data I'd like to fill the null values with the previous days value.

So when I check 07 Jan Saturday's value it should be 13

My process is I

(1) Tmp1: Load all data from EXCEL into a temporary table

(2) Tmp2: Left Join all dates from Master Calendar onto SharePrices

(3) Load All Data from Tmp2 into Main table, if value is null then make it "1"

There seems to be a problem at Step 2 because it doesnt load the share name of a company,

so I only get the date

So data would be

06 Jan (F) - S&P - 13

06 Jan (F) - Dax - 14

07 Jan (S) - null - 1

...

So the code looks something like

Load  * FROM Excel

Load Dates from Master Calendar

Left Join

Load FundDetails from ShareTable

Load * Into Master Table

Problem is really just to get values for a Saturday/Sunday (null dates) and fill it into the main table

Any suggestions?

Thank you

[ruanhaese]

Ive attached a working example.

[sunny_talwar · Accepted solution]

May be this

SharePrice:

LOAD * INLINE [

    aDate, aPrice, Fund

    '2017/01/01', 10, ABC

    '2017/01/02', 11, ABC

    '2017/01/03', 12, ABC

    '2017/01/06', 15, ABC

    '2017/01/07', 16, ABC

    '2017/01/08', 17, ABC

    '2017/01/09', 18, ABC

    '2017/01/12', 21, ABC

    '2017/01/13', 22, ABC

    '2017/01/14', 23, ABC

    '2017/01/15', 24, ABC

    '2017/01/01', 10, DEF

    '2017/01/02', 11, DEF

    '2017/01/03', 12, DEF

    '2017/01/06', 15, DEF

    '2017/01/07', 16, DEF

    '2017/01/08', 17, DEF

    '2017/01/09', 18, DEF

    '2017/01/12', 21, DEF

    '2017/01/13', 22, DEF

    '2017/01/14', 23, DEF

    '2017/01/15', 24, DEF

];

//----CALENDAR-----

SET StartDate = MakeDate( 2017, 01, 01);

SET EndDate = Today();

TempTable:

LOAD *

Where not Exists(aDate);

LOAD

  date($(StartDate) + RecNo() -1) As aDate,

  1 as aPrice

AutoGenerate

$(EndDate) - $(StartDate);

Join (TempTable)

LOAD Distinct Fund

Resident SharePrice;

Concatenate(SharePrice)

LOAD *

Resident TempTable;

DROP Table TempTable;

[ruanhaese]

Thank you!