Getting previous value if the value is blank

[mccook]

Hi All,

I have data like below:

Customer	Date	Credit Limit
140140	Dec 2015	10,000
140140	Jan 2016	12,000
140140	Feb 2016	-
140140	Mar 2016	15,000
140140	Apr 2016	-
299211	Dec 2015	-
299211	Jan 2016	130,000
299211	Feb 2016	-
299211	Mar 2016	-
299211	Apr 2016	120,000
181236	Dec 2015	-
181236	Jan 2016	-
181236	Feb 2016	-
181236	Mar 2016	-
181236	Apr 2016	55,000

What I want is for the months with no value, for them to look for a previous value for the customer and use that. e.g.

Customer	Date	Credit Limit
140140	Dec 2015	10,000
140140	Jan 2016	12,000
140140	Feb 2016	12,000
140140	Mar 2016	15,000
140140	Apr 2016	15,000
299211	Dec 2015	-
299211	Jan 2016	130,000
299211	Feb 2016	130,000
299211	Mar 2016	130,000
299211	Apr 2016	120,000
181236	Dec 2015	-
181236	Jan 2016	-
181236	Feb 2016	-
181236	Mar 2016	-
181236	Apr 2016	55,000

(note: Customer 299211 for Dec 2015 has no previous value, so is blank, just like 181236)

I was thinking I could possibly add a rowno() and  use peek(), but any advice/help appreciated.

Cheers,

Dean

[sunny_talwar]

Can you elaborate on how 181236 got back filled? for Dec 2015 till Mar 2016?

[mccook]

Hi Sunny,

Sorry, they should also be Blank in the made up example I created.

Cheers,

Dean

[sunny_talwar · Accepted solution]

If that is an error and those should all be null, then try this:

Table:

LOAD Customer,

  Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

  If(Len(Trim([Credit Limit])) > 0, [Credit Limit]) as [Credit Limit]

INLINE [

    Customer, Date, Credit Limit

    140140, Dec 2015, "10,000"

    140140, Jan 2016, "12,000"

    140140, Feb 2016

    140140, Mar 2016, "15,000"

    140140, Apr 2016

    299211, Dec 2015

    299211, Jan 2016, "130,000"

    299211, Feb 2016

    299211, Mar 2016

    299211, Apr 2016, "120,000"

    181236, Dec 2015

    181236, Jan 2016

    181236, Feb 2016

    181236, Mar 2016

    181236, Apr 2016, "55,000"

];

FinalTable:

NoConcatenate

LOAD Customer,

  Date,

  If(Len(Trim([Credit Limit])) > 0, [Credit Limit],

  If(Customer = Previous(Customer), Peek('Credit Limit'), [Credit Limit])) as [Credit Limit]

Resident Table

Order By Customer, Date;

DROP Table Table;

[jonathandienst]

This will fill forward for as many months as required:

LOAD

    Customer,

    Date,

    If(Len([Credit Limit]) = 0 And Customer = Previous(Customer), Peek([Credit Limit]), [Credit Limit]) as [Credit Limit]

RESIDENT ....

ORDER BY Customer, Date