row level source and destination

[krish2459]

Hi,

I'm attaching some sample data in the sheet "Source Data" and rquied output in the sheet "Required Output".

Please see the highlited column in the "Required Output" sheet.

Finally I want to create destination latitude and longitues using soure co-ordinates.

Thanks,

Krish

[Vegar · Accepted solution]

I recommend yo do this in the script. Load the excel/source like below.

TMP:
LOAD
   lattitude,
   Longitude,
   Source,
   Destination,
   RecNo() as RowID
FROM [Book1.xlsx] (ooxml, embedded labels, table is [Source Data])
;

FINAL:
LOAD
   RowID,
   lattitude,
   Longitude,
   Source,
   Destination,
   IF(Destination = Peek('Destination'), peek('lattitude') , null()) as [Lattitude destination],
   IF(Destination = Peek('Destination'), peek('Longitude') , null()) as [Longitude destination]
Resident TMP
Order By RowID Desc
;
DROP TABLE TMP;