topic Re: Ignore certain rows of data? in QlikView

Ignore certain rows of data?

Fri, 28 Feb 2014 11:52:54 GMT

I have some transactional data, as you can see in the chart below.

However, we have a problem in that every now and again, one half of a transaction will be conducted in one week and the second half of it will be conducted the next week, which totally skews the figures. There is always a positive value to one side of these transactions and a negative value on the other side.

You can see below, the last 5 weeks of data are correct, but the data in the first two weeks is totally wrong.

This is a long shot, but is there any way for Qlikview to somehow determine erroneous transactions such as these and automatically ignore them where the two sides of the same transaction do not occur in the same week? Or even for it to somehow 'fix' the data?

I'm a bit new to Qlikview, so apologies if this a stupid question!

Re: Ignore certain rows of data?

ThornOfCrowns — Fri, 28 Feb 2014 11:57:02 GMT

Could you post an example of the data you're using to generate that chart?

Also, have you tried putting an expression like =WeekName(Transact_date) into a straight table to see if the data then falls into better "chunks"?

Re: Ignore certain rows of data?

marcus_malinow — Fri, 28 Feb 2014 12:20:46 GMT

Hi Alex,

it sounds here like you're going to need to do some scripting to eliminate / group these records during your load.

I'm guessing here that there's an ID field that links the positive and negative values together. And a date that's associated with each record, e.g TransactionDate. And, of course a value field.

In your script, you could do something like:

LOAD

ID,

TransactionDate,

Value,

if(Peek('ID', -1) = ID, 1, 0) as CompletedTransaction,

if(Peek('ID', -1) = ID, Peek('Value', -1) + Value, 0) as CompletedTransactionValue

FROM [your source]

ORDER BY ID, TransactionDate;

This would group together pairs of ID values. Then, simply chart CompletedTransactionValue.

Marcus