Complex Expression

[#1]

Hi All,

I have a bit of a challenging situation which I thought I would ask some of the more experienced app developers for some assistance.

I have a spreadsheet which contains some odometer readings. I have created a dummy version of the data which I have attached as it may be a bit hard to explain in just words. I am trying to set up an app that analyses mileage, i.e. mileage today - mileage yesterday by field staff, as well as mileage between certain periods eg between July & Sept if I selected these in a list box.

The challenge comes in when you don't have an odometer reading for a particular day, mileage today - mileage yesterday no longer works. Also there is an aggregation problem if you ever have a situation when you don't have an odometer reading on the last or first day of the month.

Not sure how to get round this, but was thinking if there was a way to insure that there is a value for each employee for the odometer reading, by converting every blank to the last record reading for that employee (date wise) in an upload expression, or can this be handled some how when doing the actual expression for an object?

Any thoughts or guidance would be greatly appreciated.

Thank you

Herbiec09

[Gysbert_Wassenaar]

I can't open the file you attached. It seems to be corrupt.

The file wizard has some transformation options that may be of use. You can for example use the Fill option to replace missing values with the value of the row above.

You can also use the previous and/or peek functions in the load statement to retrieve values from earlier rows.

See this document for more information on generating missing data: Generating Missing Data In QlikView

[#3]

Thanks Gysbert,

I have re attached the file, not sure what had happened to it, but it was showing a size of 19mb when it should be about 100kb.

Have removed formats. Can you kindly have a peak at the data.

Thanks

[Gysbert_Wassenaar · Accepted solution]

See attached qvw. You have some weird data in there. And multiple readings per day for some names.

[#5]

Gysbert,

This is absolutely brilliant it's exactly what I needed. Yes, you are correct, the data is a bit wierd, data is being uploaded from hand held devices via gps with very little data validation imbedded into the process, but the multiple readings per day are fine, I can use the Max and Min.

Am a bit new to Qlikview and developing my first few apps. Is there a comprehensive guide on "Functions" similar to what you would get in Excel?

Any other tips and advice on going from a novice to an expert overnight...lol

Really appreciate your help

Herbiec09

[Gysbert_Wassenaar]

The help file describes the functions. Other tips... well, read all blog posts of Henric Cronström. And search community.qlik.com for answers if you have a question. You're probably not the first to run into it, so there may be solutions posted already.