SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Data:
LOAD APPL_ID,
app_date,
event_type,
Date(EVT_DTE, '$(DateFormat)') as EVT_DTE,
DecisionDate
FROM
[SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);
AnalysedLast:
LOAD
APPL_ID,
FirstValue(EVT_DTE) as AnalysedLastDate
resident Data
where event_type = 'analysed'
group by APPL_ID
order by EVT_DTE desc;
EnteredFirst:
LOAD
APPL_ID,
FirstValue(EVT_DTE) as EnteredFirstDate
resident Data
where event_type = 'entered'
group by APPL_ID
order by EVT_DTE;
