topic Re: Using multiple data lines on expression in QlikView

Using multiple data lines on expression

Fri, 11 Sep 2015 20:01:01 GMT

Hello,

I was wondering if any one can help with an issue imhaving.

First of all, my data: (DATE DD/MM/YYY)

ID	DateON	DateOFF
1	01/01/2015	15/01/2015
2	02/01/2015	13/01/2015
3	04/01/2015	24/01/2015
1	17/01/2015	25/01/2015
2	19/01/2015	24/01/2015
1	26/01/2015	31/01/2015
2	27/01/2015	30/01/2015
3	29/01/2015	31/01/2015

Now, i have a table that the first expression is the time ON, that the field DateON- DateOFF (not really hard), the problem comes when i need to get the time until hes back on, or the time hes been off.

For example, the table i want would look like:

ID	DateON	DateOFF	TimeON	TimeOFF
1	01/01/2015	15/01/2015	14	2
2	02/01/2015	13/01/2015	11	6
3	04/01/2015	24/01/2015	20	5
1	17/01/2015	25/01/2015	8	1
2	19/01/2015	24/01/2015	5	3
1	26/01/2105	31/01/2015	5
2	27/01/2015	30/01/2015	3
3	29/01/2015	31/01/2015	2

The TimeOFF colum substract the next DateON minus the actual DateOFF.

Is there a way i can do this?

I was thinking i would have to have and extra column on my data that has the next OnDate, but im not sure how to do that. I would have to lookup for matching ids, but im not sure how to do this example. Maybe using some peek inside and if clause, im not sure. It still difficult because you are not sure how many registers youll read before having the same ID again.

Thanks in advance.

Re: Using multiple data lines on expression

MK_QSL — Fri, 11 Sep 2015 20:08:27 GMT

Temp:
Load * Inline
[
  ID, DateON, DateOFF
  1, 01/01/2015, 15/01/2015
  2, 02/01/2015, 13/01/2015
  3, 04/01/2015, 24/01/2015
  1, 17/01/2015, 25/01/2015
  2, 19/01/2015, 24/01/2015
  1, 26/01/2015, 31/01/2015
  2, 27/01/2015, 30/01/2015
  3, 29/01/2015, 31/01/2015
];


Final:
Load
  ID,
  DateON,
  DateOFF,
  DateOFF - DateON as TimeON,
  If(ID = Previous(ID),Previous(DateON) - DateOFF, Null()) as TimeOFF
Resident Temp
Order By ID, DateON Desc;


Drop Table Temp;

Re: Using multiple data lines on expression

sinanozdemir — Fri, 11 Sep 2015 20:13:44 GMT

Hi,

Here you go:

And the table:

Hope this helps.

Re: Using multiple data lines on expression

Oleg_Troyansky — Fri, 11 Sep 2015 20:14:33 GMT

I'm assuming that you are doing it in your script, and each line in the example represents a line in your data table...

You need to load your data and sort it by ID and DateON, using the ORDER BY clause.

Then, you can operate with the functions Peek() and Previous() to fetch the data from the previous row. The two functions are similar, but they have their differences and they are not always interchangeable. Depending on your situation, you will need either one or the other. Without too much explanation, I can suggest this rule of thumb - use Previous() to compare the current ID with the previous ID and use Peek() in your calculations:

IF Previous() THEN Peek().

If you need to "look into the future" and calculated how long until the employee gets goes On again, you'll have to reload your data once again, this time sorting by ID and Date in the descending order, and then you can use Peek() and Previous() again.

In case you are curious about more details, check out my new book QlikView Your Business. In it, I'm demonstrating how to use Previous() and Peek() for calculating Inventory Aging balances.

best,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense