Load 1 address of 3 based on latest date

[haymarketpaul]

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?

[avinashelite · Accepted solution]

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

[PradeepReddy]

there are only 3 addresses or might change dynamically?

[haymarketpaul]

yes maximum 3 addresses

[haymarketpaul]

thanks for reply - looks like it might work nicely - i'll do some investigation

[avinashelite]

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

[PradeepReddy]

see the attachment, it might be helpfull

[haymarketpaul]

Thank you - this helps

[Digvijay_Singh]

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)

[Anonymous]

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