Join Data to row before

[wunderch]

Hi guys,

I have a table with the following data:

TTBOXE	Key	Long	Lat
278	278_001	10,99942	48,35164
278	278_007	10,95697	48,37859
278	278_012	10,90512	48,38562
280	280_001	10,99942	48,35164
280	280_005	10,70835	48,61239
280	280_008	10,86037	48,5631
280	280_100	10,79289	48,4653
280	280_105	10,85738	48,53027

Now I want a table that set the Long and Lat to the row before, so that I get the following table:

TTBOXE	Key	Long	Lat	Long_End	Lat_End
278	278_001	10,99942	48,35164	10,95697	48,37859
278	278_007	10,95697	48,37859	10,90512	48,38562
278	278_012	10,90512	48,38562
280	280_001	10,99942	48,35164	10,70835	48,61239
280	280_005	10,70835	48,61239	10,86037	48,5631
280	280_008	10,86037	48,5631	10,79289	48,4653
280	280_100	10,79289	48,4653	10,85738	48,53027
280	280_105	10,85738	48,53027

This shut be done for every TTBOXE.

Can anyone help me???

Regards Chris

[brunobertels]

Hi 

may be like that adding a rowID , the using previous et load data in reverse order 

[temp]:

LOAD

rowno() as RowID,

[TTBOXE],

[Key],

[Long],

[Lat]

; 

LOAD * INLINE 

[

TTBOXE;Key;Long;Lat

278;278_001;10,99942;48,35164

278;278_007;10,95697;48,37859

278;278_012;10,90512;48,38562

 ; ; ; 

280;280_001;10,99942;48,35164

280;280_005;10,70835;48,61239

280;280_008;10,86037;48,5631

280;280_100;10,79289;48,4653

280;280_105;10,85738;48,53027

](delimiter is ';');

final:

LOAD

RowID,

[TTBOXE],

[Key],

[Long],

[Lat],

    if([TTBOXE]=previous([TTBOXE]),previous(Long)) as Long_End,

    if([TTBOXE]=previous([TTBOXE]),previous(Lat)) as Lat_End

    resident temp Order by RowID desc ;

    drop table temp;

resulting table : 

[Kushal_Chawda · Accepted solution]

@wunderch  try below

Data:
Load TBOXE,
     Key,
     Long,
     Lat
FROM Source;

New:
NoConcatenate
Load *,
     if(TBOXE=Previous(TBOXE), Previous(Long)) as Long_End,
      if(TBOXE=Previous(TBOXE), Previous(Lat)) as Lat_End
Resident Data
Order by TBOXE,Key desc;

Drop Table Data;

[wunderch]

Thanks, this is exactly what i want.

Have a nice day!

Regards

Chris