3 Replies Latest reply: Feb 15, 2018 11:56 AM by Josefina Fasoli RSS

    help with a JOIN in a LOAD SCRIPT

    Celia Seward

      See below, this LOAD from Resident CPCfile is already in my load script.

       

      LOAD

      "DATE" as "DATE",

      month("DATE") as tmonth,

      num(month("DATE")) as tmonthnum,

      year("DATE") as tyear,

      day("DATE") as tday     

         Resident CPCFile;

       

       

      I also have this little file available that contains field schooldays which is not yet in my load script.   I want to join below with above so I can include schooldays.  How do I do the JOIN in the load script?  I suppose I could JOIN on tmonth and tyear to access schooldays like in SQL.   PLEASE HELP !!!

       

      LOAD

          tmonth,

          tyear,

          schooldays

      FROM [lib://Data files/RadnorMappingTables.xls]

      (biff, embedded labels, table is schooldays$);

        • Re: help with a JOIN in a LOAD SCRIPT
          Josefina Fasoli

          This can be useful for you: Different Join Functions in Qlikview

           

          In this case you can make a key by yearmonth. Please check first the date format in both of your tables.

          You should rename the fields with the same name in the second table in order to avoid to have a synthetic key in your model.

           

          Table1:

          LOAD

          year("DATE") &month("DATE")  as %DateKey

          "DATE" as "DATE",

          month("DATE") as tmonth,

          num(month("DATE")) as tmonthnum,

          year("DATE") as tyear,

          day("DATE") as tday   

            Resident CPCFile;

           


          Left Join (Table1)


          LOAD

              tyear&tmonth as %DateKey

              tmonth as SchoolDaysTMonth,

              tyear as SchoolDaysTYear,

              schooldays

          FROM [lib://Data files/RadnorMappingTables.xls]

          (biff, embedded labels, table is schooldays$);


          Hope it helps!

          Josefina