Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

richters
New Contributor III

How to get last row having the same ID

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):

IDEbeneIDText
11some Text
22some Text
33some Text
42some Text
51some Text
62some Text
73some Text
81some 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!

1 Solution

Accepted Solutions
nstefaniuk
Contributor III

Re: How to get last row having the same ID

Here it is

15 Replies
jason_michaelid
Honored Contributor II

Re: How to get last row having the same ID

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

richters
New Contributor III

Re: How to get last row having the same ID

Hi, here you are:

IDEbeneIDTextPrevTextWithSameID
11some Text ID1some Text ID1
22some Text ID2some Text ID2
33some Text ID3some Text ID3
42some Text ID4some Text ID2
51some Text ID5some Text ID1
62some Text ID6some Text ID2
73some Text ID7some Text ID3
81some Text ID8some 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_michaelid
Honored Contributor II

Re: How to get last row having the same ID

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
New Contributor III

Re: How to get last row having the same ID

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_michaelid
Honored Contributor II

Re: How to get last row having the same ID

In that case wouldn't your output table be like this:

IDEbeneIDTextPrevTextWithSameID
11some Text ID1some Text ID1
22some Text ID2some Text ID2
33some Text ID3some Text ID3
42some Text ID4some Text ID2
51some Text ID5some Text ID1
62some Text ID6some Text ID4
73some Text ID7some Text ID3
81some Text ID8some Text ID5

Note my changes to rows 6 and 8.

Jason.

richters
New Contributor III

Re: How to get last row having the same ID

You are totally right, sorry for that

jason_michaelid
Honored Contributor II

Re: How to get last row having the same ID

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
New Contributor III

Re: How to get last row having the same ID

Thanks, I will go on trying

nstefaniuk
Contributor III

Re: How to get last row having the same ID

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

Community Browser