topic Re: Filling in missing values in QlikView

Filling in missing values

Mon, 28 Oct 2013 18:35:49 GMT

I'm working on a project where every month, each of the 100 customers are asked various questions in a survey. The customers are very reliable in responding each month, but occasionally a few will miss a month or only answer a few of the questions. We also lose and gain new customers each month too.

We will be doing a full reload each month of the whole data set, but if a customer is missing the survey for the most recent month, then we need to use the values from the previous month they had completed.

What would be the best way to implement this reload method where we can do a full reload, yet still use old values if current values are missing? I should stress that only a few of the fields in each record may be missing, not the whole record. I'd need to find which fields are missing values, and replace them with older values.

Re: Partial incremental reload

Wed, 30 Oct 2013 16:44:12 GMT

I've gotten this to work by using the Previous() function to first check for null values and then return the previous value.

if(isnull(Survey1),Previous(Survey1),Survey1) as [Monthly Survey1]

However, how can I get this to work if the previous value is also null? If there are several null values in a row, it needs to be able to go back to the last available record.

Re: Partial incremental reload

Fri, 01 Nov 2013 19:01:23 GMT

I'm trying to use but it's not filling in any missing values. Ideas?

if(isnull(Survey1) AND [Client]=peek([Client]), peek(Survey1), Survey1) AS Survey1

Re: Filling in missing values

Gysbert_Wassenaar — Sat, 02 Nov 2013 10:07:36 GMT

If your source files also contain a date then you can use that date field to get the latest values, for example using the firstsortedvalue function.

Re: Filling in missing values

Frank_Hartmann — Sun, 03 Nov 2013 07:35:18 GMT

can you give an example using the firstsortedvalue function in case of set analysis please?

I have nearly the same problem in my application. there are two or more missing values in a row and i want qlikview to use the last availible value.