6 Replies Latest reply: Jan 30, 2017 10:35 AM by Vineeth Pujari RSS

    How to extract date in a Web Page in QlikSense

    Charlie Argento

      Greetings fellow "Qlikies"  Need some help.  I'm trying to connect to a web page to extract some data.  No biggie, however, there is one section, @4, where the date is part of  a string of data in the "Game Sheet" field.  All I want to bring is the date.  Preferably, MM/DD/YY.

      The link I'm trying to extract from is:  True North Hockey Canada Game Sheet

       

      Can anyone tell me how I can extrapolate only the Date from Section @4 of this Page.  Any guidance you can provide is greatly appreciated.

       

      Charlie Argento

      Qlik TSM

      Eastern Canada

        • Re: How to extract date in a Web Page in QlikSense
          Vineeth Pujari

          try

           

          LOAD [Game Sheet] as GAMEDATE

          FROM

          [https://www.truenorthhockey.com/asp_pages/tnhcGameSheetPrint.aspx?gameid=175948]

          (html, codepage is 1252, embedded labels, table is @4)

          Where Recno()=2;

          • Re: How to extract date in a Web Page in QlikSense
            Vineeth Pujari

            For reformatting the date try date#()

             

            Maybe

            DATE (Date#(trim (fieldname),'WWWW MMM DD, YYYY'),'MM/DD/YYYY')

            • Re: How to extract date in a Web Page in QlikSense
              Charlie Argento

              Thank you so very much Vineeth.  That worked like a charm. Now the Issue I'm having is that there is no correlation between GAMEDATE and the actual Goals/Assists Played that date. How do I apply it to the data in each summary field below?

               

              [Date]:

              LOAD

                  "Game Sheet" as GAMEDATE

              FROM [lib://Game 2]

              (html, codepage is 1252, embedded labels, table is @4)

              Where Recno()=2;

               

               

              [Teams]:

              LOAD

                  "Home: Thunderbirds",

                  "Away: Mamalukes",

                 

                 

              FROM [lib://Game 2]

              (html, codepage is 1252, embedded labels, table is @9);

              Rename Table @9 to Team;

               

               

              [Opponent Score Summary]:

              LOAD

               

                  # as Opponent#,

                 Name as Opponent_Name,

                 Goals as Opponent_Goal,

                  Assists as Opponent_Assist,

                  Points as Opponent_Points,

                  PIM as Opponent_PIM

              FROM [lib://Game 2]

              (html, codepage is 1252, embedded labels, table is @11);

              Rename Table @11 to [Opponent_Score_Summary];

               

               

              [Mamalukes Score Summary]:

              LOAD

               

                  # as [Mamaluke#],

                  Name as Mamaluke_Name,

                  Goals,

                  Assists,

                  Points,

                  PIM

              FROM [lib://Game 2]

              (html, codepage is 1252, embedded labels, table is @13);

              Rename Table @13 to [Mamalukes_Score_Summary];

                • Re: How to extract date in a Web Page in QlikSense
                  Vineeth Pujari

                  Hi Charlie,

                   

                  You can use Peek() to store the date value in a variable and use it in load statement of other tables like below

                   

                  [Date]:

                  LOAD

                      "Game Sheet" as GAMEDATE

                  FROM [lib://Game 2]

                  (html, codepage is 1252, embedded labels, table is @4)

                  Where Recno()=2;

                   

                  Let vGameDate = purgechar(trim(Peek('GAMEDATE',0,'[Date]')),',');

                  Let vDateIS = MakeDate(SubField('$(vGameDate)',' ',4),Month(DATE#(SubField('$(vGameDate)',' ',2),'MMM')),SubField('$(vGameDate)',' ',3));


                  [Teams]:

                  LOAD

                      "Home: Thunderbirds",

                      "Away: Mamalukes"

                   

                   

                  FROM [lib://Game 2]

                  (html, codepage is 1252, embedded labels, table is @9);

                  Rename Table @9 to Team;

                   

                   

                   

                  [Opponent Score Summary]:

                  LOAD

                   

                      # as Opponent#,

                     Name as Opponent_Name,

                     Goals as Opponent_Goal,

                      Assists as Opponent_Assist,

                      Points as Opponent_Points,

                      PIM as Opponent_PIM,

                      '$(vDateIS )' as GameDATE

                  FROM [lib://Game 2]

                  (html, codepage is 1252, embedded labels, table is @11);

                  Rename Table @11 to [Opponent_Score_Summary];

                   

                  [Mamalukes Score Summary]:

                  LOAD

                   

                      # as [Mamaluke#],

                      Name as Mamaluke_Name,

                      Goals,

                      Assists,

                      Points,

                      PIM,

                     '$(vDateIS)' as GameDATE

                  FROM [lib://Game 2]

                  (html, codepage is 1252, embedded labels, table is @13);

                  Rename Table @13 to [Mamalukes_Score_Summary];