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