3 Replies Latest reply: Sep 26, 2016 4:11 PM by Stefan Wühl RSS

    Join after using IntervalMatch

    Christopher Ellis

      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