Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Using multiple data lines on expression

Hello,

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

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

IDDateONDateOFF

1

01/01/201515/01/2015
202/01/201513/01/2015
304/01/201524/01/2015
117/01/201525/01/2015
219/01/201524/01/2015
126/01/201531/01/2015
227/01/201530/01/2015
329/01/201531/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:

IDDateONDateOFFTimeONTimeOFF
101/01/201515/01/2015142
202/01/201513/01/2015116
304/01/201524/01/2015205
117/01/201525/01/201581
219/01/201524/01/201553
126/01/210531/01/20155
227/01/201530/01/20153
329/01/201531/01/20152

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.

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Using multiple data lines on expression

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

View solution in original post

3 Replies
MVP
MVP

Re: Using multiple data lines on expression

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;

sinanozdemir
Valued Contributor III

Re: Using multiple data lines on expression

Hi,

Here you go:

Capture.PNG

And the table:

Capture.PNG

Hope this helps.

MVP & Luminary
MVP & Luminary

Re: Using multiple data lines on expression

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

View solution in original post