Peek() with two conditions

[MadiF]

Hi all, I'm wondering if I can sort-of do a conditional peek / peek with match type of combo. The question I'm trying to ask at each line is: after loading in Date 1 and ID order, has there been a previously loaded record for this ID that had a Date 2 greater than the current line's Date 1? If so, Y, else N. I've been able to achieve this logic using a combo of peek, rangemax and a loop that goes thru each unique ID and then concatenates all the results together. The problem is, I have 10,000 unique IDs and the loop is extremely slow and unsustainable. If I don't loop, I get the 'current result' in the mock table below, because even if I use an if statement (if match(ID, previous (ID))), when that returns true and it peeks at prev values it peeks at all the prev loaded values in the table. I want it to evaluate 'if ID match prev ID' first, and when that returns true, 'peek at prev loaded records for this ID only and compare max value in Date 2 to current line Date 1'. Hopefully that's enough details to explain my problem but please let me know if there's anything else I can provide. Again, my goal is to get away from the loop structure I currently have (which removes the peek issue, but takes far too long).

Record |  ID |  Date 1 | Date 2 | Current Result | Goal Result 

1             | A3 | 2/1/17  | 2/6/17 |                -              | -

2             | A3 | 2/3/17 | 2/6/17 |                 Y             | Y

3             | A3 | 2/14/17  | 2/17/17 |           N            | N

4             | A3 | 2/25/17  | 2/26/17 |           N            | N

5             | B6 | 2/12/17  | 2/13/17 |           -             |  -

6             | B6 | 2/15/17  | 2/19/17 |           Y             | N

7             | B6 | 2/18/17  | 2/28/17 |           Y             | Y

Thanks in advance!

[mwanders]

Hi,

I'm a little bit stuck on the explanation:

The question I'm trying to ask at each line is: after loading in Date 1 and ID order, has there been a previously loaded record for this ID that had a Date 2 greater than the current line's Date 1? If so, Y, else N

Ok, you sorted the table on ID and Date1. Then you want to evaluate if the Date2 from the evaluated row is greater than the current line. What is the current line?

If I look only at ID A3 every date is greater then the previous row but still I see a N for the last two rows in your result. Do I miss something? If this is more clear to me then maybe I can help you.

[MadiF]

I'm comparing date field 2 to date field 1; on line 3 the result should be 'N' because there has not been a date in date field 2 that is larger than date field 1. Line 3 date field 1 is 2/14/17, the only date field 2 values loaded so far are lines 1 and 2 which are both 2/6/17

[MartijnWanders]

Hi,

Since today I can not log on with my normal (old) account anymore. I think I understand what you want to achieve. I created something in a testscript. You can copy paste this in your own script.

I attempted to avoid the loop. I hope that I'm in the right direction. I added myself a new row (Record 2) to create a date2 that is greater than the date2 in the next row. In your example it's always greater then the previous one. I think its possible that this is not always the case.

temp1:
load * inline [
Record, ID, Date 1, Date 2
1, A3, 2/1/17, 2/6/17
2, A3, 2/1/17, 2/15/17
3, A3, 2/3/17, 2/6/17
4, A3, 2/14/17, 2/17/17
5, A3, 2/25/17, 2/26/17
6, B6, 2/12/17, 2/13/17
7, B6, 2/15/17, 2/19/17
8, B6, 2/18/17, 2/28/17
];

temp:
LOAD
*,
IF([Check first row of ID] = 0, 'N',
IF(Date1 < [Max date2 in range], 'Y', 'N')) AS [Check date];
LOAD
*,
IF(PEEK(ID)= ID, PREVIOUS([Max date2 in rangetemp]), [Max date2 in rangetemp]) AS [Max date2 in range]; // move date 1 row below
LOAD
*,
IF(ROWNO() = 1, Date2, DATE(IF(PEEK(ID)= ID,
RANGEMAX(Date2, PEEK([Max date2 in rangetemp])),Date2),'DD-MM-YYYY')) AS [Max date2 in rangetemp]; // check max date in range but take als current row
LOAD
Record,
ID,
DATE(DATE#(REPLACE([Date 1],'/','-'),'MM-DD-YYYY')) AS Date1,
DATE(DATE#(REPLACE([Date 2],'/','-'),'MM-DD-YYYY')) AS Date2,
IF(ID = PREVIOUS(ID), 1, 0) AS [Check first row of ID] // first is always N
RESIDENT temp1;

DROP TABLE temp1;