Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
QV11 SR4
I need to select the latest address based on the latest date where one exists eg...
Dateformat DD/MM/YY
CMS Date | CMS Address1 | DSB Date | DSB Address1 | MIMS Date | MIMS Address1 |
---|---|---|---|---|---|
03/11/15 | 10 Richmond Road | 04/05/15 | 126 Muirs Lane | 09/02/15 | 555 Dunbar Street |
I know i can use Date(RangeMax.... to quickly find the latest date of the 3 and
In the example above the CMS Date is the latest so i would use CMS Address1 as the latest Address1 which is fine BUT if CMS Address1 is empty i would then want to take the next available Address1 (DSB in the example above) and so on with the MIMS Address1 if the DSB one is empty.
I can satisfy this logic in a lengthy IF statement but am wondering if there's a better more efficient way to handle this?
Any advice?
Try like this :
combine all your date and address to single table like this
EG
Temp:
LOAD ID,
[CMS Date] as New_Date,
[CMS Address1] as Address
resident
orginal_table;
LOAD ID,
[MIMS Date] as New_Date,
[MIMS Address1] as Address
resident
orginal_table;
LOAD ID,
[DSB Date] as New_Date,
[DSB Address1] as Address
resident
orginal_table;
//then use get the max date like this
result:
LOAD ID,
max(New_Date) as Date,
Address
resident
Temp
where len(trim(Address))>0
drop table Temp;
result table will give you the results that your looking for
Try like this :
combine all your date and address to single table like this
EG
Temp:
LOAD ID,
[CMS Date] as New_Date,
[CMS Address1] as Address
resident
orginal_table;
LOAD ID,
[MIMS Date] as New_Date,
[MIMS Address1] as Address
resident
orginal_table;
LOAD ID,
[DSB Date] as New_Date,
[DSB Address1] as Address
resident
orginal_table;
//then use get the max date like this
result:
LOAD ID,
max(New_Date) as Date,
Address
resident
Temp
where len(trim(Address))>0
drop table Temp;
result table will give you the results that your looking for
there are only 3 addresses or might change dynamically?
yes maximum 3 addresses
thanks for reply - looks like it might work nicely - i'll do some investigation
NOTE: you need to have key field for this logic (in my example ID )
If you don't have the key column try to generate using the rowno() or autonummber() on the source table
see the attachment, it might be helpfull
Thank you - this helps
See if below works, might be simplest but didn't try yet. It will show the latest and pick the one which is not null.
Firstsortedvalue(alt(CMS Addr, DSB Addr, MIMS Addr),-Date)
Hi,
You should load with the script a new table wich will have the needed information.
Here is what you need:
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='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';
SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=0;
SET FirstMonthOfYear=0;
SET CollationLocale='';
TABLE:
LOAD * INLINE [
CMS Date, CMS Address1, DSB Date, DSB Address1, MIMS Date, MIMS Address1
03/11/15, 10 Richmond Road, 04/05/15, 126 Muirs Lane, 09/02/15, 555 Dunbar Street
01/02/2015, test1, 05/05/2017, test2 ,05/05/2000, test3];
TABLE_RESULT:
LOAD
[CMS Date] as Date,
[CMS Address1] as Adress,
'CMS' as Type,
[CMS Date]&' - '&[CMS Address1]&' - '&[DSB Date]&' - '&[DSB Address1]&' - '&[MIMS Date]&' - '&[MIMS Address1] as %KEY
RESIDENT
TABLE
where len([CMS Address1])>0;
LOAD
[DSB Date] as Date,
[DSB Address1] as Adress,
'DSB' as Type,
[CMS Date]&' - '&[CMS Address1]&' - '&[DSB Date]&' - '&[DSB Address1]&' - '&[MIMS Date]&' - '&[MIMS Address1] as %KEY
RESIDENT
TABLE
where len([DSB Address1])>0;
LOAD
[MIMS Date] as Date,
[MIMS Address1] as Adress,
'MIMS' as Type,
[CMS Date]&' - '&[CMS Address1]&' - '&[DSB Date]&' - '&[DSB Address1]&' - '&[MIMS Date]&' - '&[MIMS Address1] as %KEY
RESIDENT
TABLE
where len([MIMS Address1])>0;
LAST_ADRESS:
LOAD
FirstSortedValue(Date,-Date) as LastDate,
FirstSortedValue(Adress,-Date) as Adress,
FirstSortedValue(Type,-Date) as Type,
%KEY
RESIDENT
TABLE_RESULT
GROUP BY
%KEY
;
DROP TABLE TABLE_RESULT;
LEFT Join (TABLE)
LOAD
LastDate as Date,
Adress,
SubField(%KEY,' - ',1) as [CMS Date],
SubField(%KEY,' - ',2) as [CMS Address1],
SubField(%KEY,' - ',3) as [DSB Date],
SubField(%KEY,' - ',4) as [DSB Address1],
SubField(%KEY,' - ',5) as [MIMS Date],
SubField(%KEY,' - ',6) as [MIMS Address1],
Type
RESIDENT
LAST_ADRESS;
DROP TABLE LAST_ADRESS;
Here is the result:
CMS Date | CMS Address1 | DSB Date | DSB Address1 | MIMS Date | MIMS Address1 | Date | Adress | Type |
03/11/15 | 10 Richmond Road | 04/05/15 | 126 Muirs Lane | 09/02/15 | 555 Dunbar Street | 03/11/15 | 10 Richmond Road | CMS |
01/02/2015 | test1 | 05/05/2017 | test2 | 05/05/2000 | test3 | 05/05/2017 | test2 | DSB |