Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
dean_mccook
Contributor

Getting previous value if the value is blank

Hi All,

I have data like below:

CustomerDateCredit Limit
140140Dec 201510,000
140140Jan 201612,000
140140Feb 2016-
140140Mar 201615,000
140140Apr 2016-
299211Dec 2015-
299211Jan 2016130,000
299211Feb 2016-
299211Mar 2016-
299211Apr 2016120,000
181236Dec 2015-
181236Jan 2016-
181236Feb 2016-
181236Mar 2016-
181236Apr 201655,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.

CustomerDateCredit Limit
140140Dec 201510,000
140140Jan 201612,000
140140Feb 201612,000
140140Mar 201615,000
140140Apr 201615,000
299211Dec 2015-
299211Jan 2016130,000
299211Feb 2016130,000
299211Mar 2016130,000
299211Apr 2016120,000
181236Dec 2015-
181236Jan 2016-
181236Feb 2016-
181236Mar 2016-
181236Apr 201655,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

1 Solution

Accepted Solutions

Re: Getting previous value if the value is blank

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;


Capture.PNG

4 Replies

Re: Getting previous value if the value is blank

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

dean_mccook
Contributor

Re: Getting previous value if the value is blank

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

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;


Capture.PNG

MVP
MVP

Re: Getting previous value if the value is blank

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser