Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
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.