topic Re: How to get last row having the same ID in QlikView

How to get last row having the same ID

richters — Tue, 16 Oct 2012 07:23:25 GMT

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!

Re: How to get last row having the same ID

Jason_Michaelides — Tue, 16 Oct 2012 07:45:40 GMT

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

Re: How to get last row having the same ID

richters — Tue, 16 Oct 2012 07:50:46 GMT

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.

Re: How to get last row having the same ID

Jason_Michaelides — Tue, 16 Oct 2012 07:56:26 GMT

Got it. Try this:

Data:

LOAD

,EbeneID

,Text

FROM .....;

JOIN (Data)

LOAD

EbeneID

,FirstValue(Text) AS PrevTextWithSameID

RESIDENT Data

GROUP BY EbeneID;

Hope this helps,

Jason

Re: How to get last row having the same ID

richters — Tue, 16 Oct 2012 08:02:02 GMT

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.

Re: How to get last row having the same ID

Jason_Michaelides — Tue, 16 Oct 2012 08:04:36 GMT

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.

Re: How to get last row having the same ID

richters — Tue, 16 Oct 2012 08:07:58 GMT

You are totally right, sorry for that

Re: How to get last row having the same ID

Jason_Michaelides — Tue, 16 Oct 2012 08:13:42 GMT

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

Re: How to get last row having the same ID

richters — Tue, 16 Oct 2012 08:15:58 GMT

Thanks, I will go on trying

Re: How to get last row having the same ID

nstefaniuk — Tue, 16 Oct 2012 08:18:21 GMT

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

Re: How to get last row having the same ID

Jason_Michaelides — Tue, 16 Oct 2012 08:21:08 GMT

That gives the solution to his output table as given, but unfortunately the actual required output is the table in my subsequent post.

Good point about script vs UI though - richters, does the solution need to be based on user-selections?

Re: How to get last row having the same ID

richters — Tue, 16 Oct 2012 08:23:55 GMT

No,

this has to be in the loading script because this is just the basis of further work in the script.

Re: How to get last row having the same ID

nstefaniuk — Tue, 16 Oct 2012 09:44:15 GMT

Oh in that case it's really easy, just use FirstSortedValue combined with a filter (with If clause) to get the max-ID-with-same-EbeneID-but-inferior-to-current-ID

Re: How to get last row having the same ID

nstefaniuk — Tue, 16 Oct 2012 09:57:43 GMT

Here it is

Re: How to get last row having the same ID

Jason_Michaelides — Tue, 16 Oct 2012 09:59:01 GMT

Nice

Re: How to get last row having the same ID

richters — Tue, 16 Oct 2012 10:04:35 GMT

Well done, thank you. I have to look at it in detail later on. Thanks!