Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
until now I could always get the last record by using peek(...,-1) or previous(). But now I need the last record where my field 'EbeneID' is the same. So I load data like this (just ignore the text is always the same, just for showing issues):
ID | EbeneID | Text |
---|---|---|
1 | 1 | some Text |
2 | 2 | some Text |
3 | 3 | some Text |
4 | 2 | some Text |
5 | 1 | some Text |
6 | 2 | some Text |
7 | 3 | some Text |
8 | 1 | some Text |
Now I want to get the text of the previous record with the same EbeneID without order the table by EbeneID (in real the table is much bigger, and it is not really an ID, its all about HTML-Code, so it is not possible to order the table and get the right text each!)
I need something like:
previous(text where previous(EbeneID) = EbeneID) , sure this doesn't work
Thanks in advance!
Here it is
Just to be clear - please post what your output table should be given the above input table.
Hi, here you are:
ID | EbeneID | Text | PrevTextWithSameID |
---|---|---|---|
1 | 1 | some Text ID1 | some Text ID1 |
2 | 2 | some Text ID2 | some Text ID2 |
3 | 3 | some Text ID3 | some Text ID3 |
4 | 2 | some Text ID4 | some Text ID2 |
5 | 1 | some Text ID5 | some Text ID1 |
6 | 2 | some Text ID6 | some Text ID2 |
7 | 3 | some Text ID7 | some Text ID3 |
8 | 1 | some Text ID8 | some Text ID1 |
I just added the ID to the Text so that it is very clear. Surely the last column should come from the Text-Column.
Got it. Try this:
Data:
LOAD
ID
,EbeneID
,Text
FROM .....;
JOIN (Data)
LOAD
EbeneID
,FirstValue(Text) AS PrevTextWithSameID
RESIDENT Data
GROUP BY EbeneID;
Hope this helps,
Jason
Well, this would always reference to the first Text of the ID but what I am searching is the reference to the text before, because it might change.
In that case wouldn't your output table be like this:
ID | EbeneID | Text | PrevTextWithSameID |
---|---|---|---|
1 | 1 | some Text ID1 | some Text ID1 |
2 | 2 | some Text ID2 | some Text ID2 |
3 | 3 | some Text ID3 | some Text ID3 |
4 | 2 | some Text ID4 | some Text ID2 |
5 | 1 | some Text ID5 | some Text ID1 |
6 | 2 | some Text ID6 | some Text ID4 |
7 | 3 | some Text ID7 | some Text ID3 |
8 | 1 | some Text ID8 | some Text ID5 |
Note my changes to rows 6 and 8.
Jason.
You are totally right, sorry for that
No worries. Can't see an obvious solution but maybe something with Peek() and a resetting variable using ID or RowNo()...or maybe look at FieldValue() or FieldIndex().
I will try and come back to this later but I need to get on with my own work now I'm afriad!
Jason
Thanks, I will go on trying
2 solutions on the fly (I guess you need to calculate it on the fly, not in script)
Finding the ID is easy, but retrieving the corresponding text is harder