Load from Excel and add a max() column

[pmaland]

I'm looking for how to load data from an Excel file but add a column as below (as I'd do it in SQL):

Select *, (select max(received) from Excel_file where wo=e.wo and charindex('test',desc)>0) as most_recent from Excel_file as e

Where the data would be:

wo, desc, received

1,'test',2/1/2021

1,'test',2/5/2021

1,'rel',2/4/2021

[anthonyj]

Hi,
To get the maximum value you can follow the below steps and add to a table you can do the following:

1. First you obtain the maximum value from the received column by using the "Max( )" function.
2. Next you save this value into a variable  (vMaxDate).
3. Load in the max date as a new column into your data.
4. Drop the temporary MaxDate table and clear the variable vMaxDate

MaxDate:
load
max(received) as maxDate
from Excel_file.xlsx;

let vMaxDate = peek('maxDate',0,'MaxDate');

Final:
load
*,
date($(vMaxDate)) as LatestDate
from Excel_file.xlsx;

drop table MaxDate;
let vMaxDate = null();

I hope this helps.

Regards
Anthony

[pmaland]

Thanks.  It looks like this would set one max date for all 3 records in the table (2/5/2021 based on my small sample data).  I would want the output to be like this:

wo,desc,received,most_recent

1,'test',2/1/2021,2/5/2021

1,'test',2/5/2021,2/5/2021

1,'rel',2/4/2021,2/4/2021

I was trying to compare the most recent test date for a selection of wo's to a date from another table and count how many were late but was getting the error about multiple aggregations so I thought about calculating the most recent date on the data load to eliminate that aggregation step later.

[anthonyj]

Hi,

Sorry about that. The best way you can do this is with a mapping table and a group by. This will use "desc" as the key to create a table with the result of the maximum value by "desc". The good thing about mapping tables is that they're not persisted into the model so there's no need to explicitly drop them. 

To use mapping tables first you must name your table. It's not necessary to suffix it with 'Map' but it helps with identifying the tables. You must use the keyword "mapping" after the table name and the first column named in the table must be the key.

MaxDateMap:
mapping
load
desc,
max(received) as maxDate
from Excel_file.xlsx
group by desc;

Final:
load
wo,
desc,
received,
date(applymap('MaxDateMap', desc, null())) as most_recent
from Excel_file.xlsx;

Second time luck I hope.

Thanks

Anthony