topic Re: Getting previous value if the value is blank in QlikView

Getting previous value if the value is blank

mccook — Thu, 28 Jul 2016 09:26:29 GMT

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

Re: Getting previous value if the value is blank

sunny_talwar — Thu, 28 Jul 2016 09:35:59 GMT

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

Re: Getting previous value if the value is blank

mccook — Thu, 28 Jul 2016 09:37:20 GMT

Hi Sunny,

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

Cheers,

Dean

Re: Getting previous value if the value is blank

sunny_talwar — Thu, 28 Jul 2016 09:38:54 GMT

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;

Re: Getting previous value if the value is blank

jonathandienst — Thu, 28 Jul 2016 09:45:19 GMT

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