topic Re: Find missing (not empty, missing) rows in QlikView

Find missing (not empty, missing) rows

Fri, 10 Aug 2012 15:19:36 GMT

is it possible, to find missing rows (missing, not empty) in tables ?

is it possible to associate them f.e. with other data sets which were created at the same time, to find the ones missing ?

There is f.e. a temperature sensor (A), which logs data (ID, hour, temp) to a table. If the power is out, it will off course not log data.
If the power is back on, it will continue, and use the current time for new data.
A similar unit (B) is working fine the whole time.

So (B) has looged 12 of 12 hours, but (A) has logged just 5 of 12 hours.
IF (A) is selected, the "hours" listbox shows the missing values (the hours without data).

Is it possible to get the same results with an expression ?

i have allready tried the usual epressions to find NULL values, this doesnt work. the field is not empty, the whole row is missing,

because of the missing rows, peek() is not working too.

Is it possible to check every hour, if there is a corresponding DATA field. if not, 'NULL' ?

an example is attached

have a nice weekend

regards,

alex

Re: Find missing (not empty, missing) rows

Fri, 10 Aug 2012 19:04:24 GMT

Do you just need a means to identify the gap's in the data? I modified your example to quickly help you identify said gaps. It will just display a red background in the cell if the value isn't greater than zero.

Or, do you need it to be more complex and have it tell you specifically which data points are missing?

Re: Find missing (not empty, missing) rows

Tue, 14 Aug 2012 08:05:45 GMT

Exactly, thats the goal - i just could not find the right word.

thank you.

coloring (and showing) the gaps is one step closer to the solution.

but, if there are lots of data, marking the gaps wont be enough.
im looking for a way to show only the gaps in one table, to have an overview of all the missing data gaps.

this should be possible, as there are some graphical ways qlikview shows the gaps, but i could not find an expression, to do the same - well, to the benefit that it can be used in other expressions.

So, it gets more complex.

Any suggestions ?

regards,

Alex