topic Join Data to row before in App Development

Join Data to row before

wunderch — Fri, 12 May 2023 10:29:29 GMT

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

Re: Join Data to row before

brunobertels — Fri, 12 May 2023 11:16:24 GMT

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 :

Re: Join Data to row before

Kushal_Chawda — Fri, 12 May 2023 11:59:49 GMT

@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;

Re: Join Data to row before

wunderch — Mon, 15 May 2023 08:52:38 GMT

Thanks, this is exactly what i want.

Have a nice day!

Regards

Chris