Join tables on id

[martyn_birzys]

I have a particular structure in the Agenda table, where I need to transform the date into another field.

I was expecting outer join to link on id, but I'm getting duplicate id rows and date field not linked to appropriate record.

Please see file attached.

[maxgro · Accepted solution]

not sure to understand

SET LongMonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;October;Nov;Dec';

Agenda:

LOAD * INLINE [

    id, City

    1, Tuesday 21st October 2014

    2, Paris,

    3, New York,

    4, Wednesday 22nd October 2014,

    5, London,

    6, Tokyo

];

tmp:

load

  id, City,

  MeetingDate,

  Date(Date#(

  left(subfield(MeetingDate, ' ', 2), len(subfield(MeetingDate, ' ', 2))-2)

  & '-' & subfield(MeetingDate, ' ', 3)

  & '-' & subfield(MeetingDate, ' ', 4),

  'DD-MMMM-YYYY'))  as NewMeetingDate;

NoConcatenate

load *,

if(WildMatch(City, '*2014*'), City, peek(MeetingDate)) as MeetingDate

Resident Agenda

order by id;

DROP Table Agenda;

[martyn_birzys]

Thanks very much, peek() does the job. I was trying previous(), which is very similar, but was getting error.