Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chrisellis
Contributor

Join after using IntervalMatch

Could anyone help please,

When I run the code below I end up with 2 tables one called Table1 and the other called Calender linked on [Mark date]. This works ok but I want to add the [Academic Year] to Table1 and drop the Calender and fileds [TStart] and [TEnd].

Could some explain what I need to do this because everytime I try it multiplies the records up in Table1 by the number of rows in Calender.

Table1:

LOAD

          * ,

          Date#("Mark date",'DD MMMM YYYY') As "Mark date", 

FROM [lib://Data/Table1.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Calender:

LOAD

  "Academic Year",

        Date#([date1], 'DD MMMM YYYY') AS TStart,

      Date#([date2], 'DD MMMM YYYY') AS TEnd

FROM [lib://Data/Calender.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

join Table1:

IntervalMatch([Mark date])

LOAD DISTINCT [TStart],

  [TEnd]

Resident Calender;

Many Thanks

Chris

3 Replies
MVP
MVP

Re: Join after using IntervalMatch

Maybe by using another LEFT JOIN:

Table1:

LOAD

     Date#("Mark date",'DD MMMM YYYY') As "Mark date"

INLINE [

Mark date

22 Sep 2016

03 Jun 2016

31 Jan 2015

];

Calender:

LOAD

  "Academic Year",

        Date#([date1], 'DD MMMM YYYY') AS TStart,

      Date#([date2], 'DD MMMM YYYY') AS TEnd

INLINE [

Academic Year, date1, date2

2015, 01 Jan 2015, 31 Dec 2015

2016, 01 Jan 2016, 31 Dec 2016

];

join (Table1)

IntervalMatch([Mark date])

LOAD DISTINCT [TStart],

  [TEnd]

Resident Calender;

LEFT JOIN (Table1)

LOAD * RESIDENT Calender;

DROP TABLE Calender;

DROP FIELD TStart, TEnd;

chrisellis
Contributor

Re: Join after using IntervalMatch

Thanks for the help, sorry no it multiplies the records in Table1 instead of just adding the column.

Chris

MVP
MVP

Re: Join after using IntervalMatch

The sample code should produce a single table containing

Mark date Academic Year
31 Jan 20152015
03 Jun 20162016
22 Sep 20162016

Do you see something else?

If your records get duplicated, I think your mark dates could be associated to more than one academic year.

Could this be the case?

Do you need to include a key to match your years (You can do this with the extended version syntax:

IntervalMatch (Extended Syntax) ‒ QlikView