topic Re: Join tables on id in QlikView

Join tables on id

martyn_birzys — Wed, 22 Oct 2014 15:53:25 GMT

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 — Wed, 22 Oct 2014 17:38:45 GMT

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 — Sat, 25 Oct 2014 21:02:27 GMT

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