docs/For loops with cross apply.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt22",
   "metadata": {},
   "source": [
    "# For loops with cross apply\n",
    "\n",
    "You can run the cells below directly in LUSID's JupyterHub.\n",
    "\n",
    "The `%%luminesce` is a magic command which passes the cell query string to Lumipy,\n",
    "which then returns a DataFrame.\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds220",
   "metadata": {},
   "source": [
    "#### Step 1: Create instrument upsert view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs220",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- In this file, we create view which uploads instruments\n",
    "-- from a CSV file based on an EffectiveDate\n",
    "-- ============================================================\n",
    "\n",
    "@v =\n",
    "use Sys.Admin.SetupView\n",
    "--provider=TestInstrumentWriter\n",
    "--parameters\n",
    "FileName,Text,/luminesce-examples/instrument-test.csv,false\n",
    "EffectiveFrom,DateTime,2020-01-01,false\n",
    "-----------------\n",
    "\n",
    "--Can't pass table parameters to the View, so for now have to read the file each time from Drive\n",
    "--Could have a separate query to chunk the files\n",
    "@@file = select #PARAMETERVALUE(FileName);\n",
    "\n",
    "@data = use Drive.Csv with @@file\n",
    "--file={@@file}\n",
    "enduse;\n",
    "\n",
    "@@dt = select datetime(#PARAMETERVALUE(EffectiveFrom));\n",
    "\n",
    "@toWrite = select * from @data where EffectiveFrom = @@dt;\n",
    "\n",
    "select * from Lusid.Instrument.Writer where ToWrite = @toWrite and EffectiveFrom = @@dt;\n",
    "\n",
    "enduse;\n",
    "\n",
    "select * from @v\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds221",
   "metadata": {},
   "source": [
    "#### Step 2: Loop over csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs221",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "--============================================================\n",
    "-- Description:\n",
    "-- In this query, we loop over rows in a CSV file by date,\n",
    "-- and load the batches to an instruments writer view\n",
    "-- This is an example of how to implement a for loop\n",
    "-- in Luminesce. For more details, see this page:\n",
    "-- https://support.lusid.com/knowledgebase/article/KA-01692/en-us\n",
    "--============================================================\n",
    "\n",
    "@@file = select '/luminesce-examples/instrument-test.csv';\n",
    "\n",
    "@data = use Drive.Csv with @@file\n",
    "--file={@@file}\n",
    "enduse;\n",
    "\n",
    "@ef = select distinct(EffectiveFrom) as Value from @data;\n",
    "\n",
    "select\n",
    "    ef.Value,\n",
    "    r.LusidInstrumentId,\n",
    "    r.WriteErrorCode\n",
    "from\n",
    "    @ef ef\n",
    "    cross apply\n",
    "    (\n",
    "        select * from TestInstrumentWriter w where w.FileName = @@file and w.EffectiveFrom = ef.Value\n",
    "    ) r\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}