How to get last row having the same ID

[richters]

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!

[Jason_Michaelides]

Just to be clear - please post what your output table should be given the above input table.

[richters]

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.

[Jason_Michaelides]

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

[richters]

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.

[Jason_Michaelides]

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.

[richters]

You are totally right, sorry for that

[Jason_Michaelides]

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

[richters]

Thanks, I will go on trying

[nstefaniuk]

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