Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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;
@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;
@DarinLevesque updated previous reply