Return data from other rows

[#1]

Given the sample source table below, I am trying to find a way to populate the Value of Key=(3,4,5) with the Value from Key=2.  I am interested both in how to do this in the LOAD statement and in an expression with some sort of complex aggregation.

Let's assume, I get the below data as not directly from the database.  How could i transform the numbers and reference the values in other records of the table?

For creating this in a Chart, I have a chart with a vertical dimension of an alternatekey (essentially all 5 records below would have the same alterantekey) and a horizontal dimension of Bucket.  How do I get the value in the Alt Bucket dimension to populate with 2 from the End Bucket Dimension.  I have tried various Set Analysis expressions, but can't seem to return the value from the 'End' bucket in the 'Alt' bucket.

Key	Type	Bucket	Value
1	H	Begin	1
2	H	End	2
3	T	Alt	0
4	T	Alt2	0
5	T	Alt3	0

[#2]

Hi Mark,

With your data set you could use the peek function on import. See example with your data below:

data:
LOAD * Inline
[
Key, Type, Bucket, Value
1, H, Begin, 1
2, H, End ,2
3 ,T, Alt ,0
4, T, Alt2, 0
5, T, Alt3, 0
];

data_temp:
NoConcatenate
LOAD Key,
Type,
Bucket,
if(Value = 0, Peek(Value), Value) as Value
RESIDENT data;

Drop table data;
RENAME table data_temp to data

Hope that helps.

Matt

[#3]

HI Mark,

Were you able to solve your problem with this?

Best,

Matt

[#4]

Haven't had much chance to look into this due to the holidays, but a pure peek, i don't think will work, possibly with some hardcoding of ordering the sql.  although this sets me on the right path, perhaps some combination of lookup(), fieldindex(), etc...

Another part of the problem is that an End record may not exist, in which case i need to Begin record, but that may also not exist in which case i need to do something else...  Perhaps some forced order that will represent it as above and peek at the previous records to confirm that they have the values i need.  Will need to play around with it and see what I can cobble together.

[#5]

Ok, good luck!

[#6]

This is difficult to do at the data level due to the variability of the data, but I was able to use the below to do it in the report.  the total keyword being the key.

sum({<BYA_BUCKET={'End'}>}TOTAL BASE_BOOK_VALUE)