Split lat long from different rows

DarinLevesque — Mon, 20 Dec 2021 21:08:46 GMT

I have data that is arranged like below:

id	value	path
194562	-94.58462	1
194562	34.452312	1
194562	-94.26451	2
194562	32.561232	2
198723	-93.894564	3
198723	33.769423	3

The Value field has a longitude and a latitude for each path number and they are related to various project numbers (the id's).

I've tried to create a variable and dynamically create a field alias based upon whether the row had a longitude (starting with a - sign, or a latitude, but that didn't appear to do the trick (see below):

Let LatLong = if(Left(@Value,1)='-', 'long',if(Left(@Value,1)<>'-', 'lat')); temp: left Join(attributes) load fc_system_id, __KEY_paths_u0, if(Left(@Value,1)='-', [@Value],if(Left(@Value,1)<>'-', [@Value])) as $(LatLong) Resident attributes;

I also tried a generic load, but with such a large data set that didn't makes sense.

Am I missing an easier way to go about this? ideally I'd like one row per path number but I'm stumping myself on this one.

Re: Split lat long from different rows

Kushal_Chawda — Mon, 05 Oct 2020 21:55:11 GMT

@DarinLevesque try below

Data: LOAD Path, ID, subfield(Lat_Long,';') as Long, subfield(Lat_Long,';',2) as Lat; LOAD Path, concat(distinct ID) as ID, Concat (Value,';',Value) as Lat_Long FROM Table group by Path;

Re: Split lat long from different rows

Kushal_Chawda — Mon, 05 Oct 2020 21:55:29 GMT

@DarinLevesque updated previous reply

topic Split lat long from different rows in App Development

Split lat long from different rows

Re: Split lat long from different rows

Re: Split lat long from different rows