Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Filling in missing values

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.

4 Replies
Not applicable

Re: Partial incremental reload

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.

Not applicable

Re: Partial incremental reload

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

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.

talk is cheap, supply exceeds demand
Honored Contributor

Re: Filling in missing values

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.

Community Browser