How to extract date in a Web Page in QlikSense

[#1]

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

[vinieme12 · Accepted solution]

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;

[vinieme12]

For reformatting the date try date#()

Maybe

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

[#4]

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];

[vinieme12]

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];

[#6]

Thank you for your insights Vineeth.  That doesn't seem to work for my model  the GameDATE Field is blank once the data is loaded.  It also causes synthetic keys to occur in the model.  Any further suggestions would be greatly appreciated.  Thank you...

[vinieme12]

I don't have QlikSense on the machine that i'm using right now

But here is my script from Qlikview and it works well

[Date]:

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;

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

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

[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  [https://www.truenorthhockey.com/asp_pages/tnhcGameSheetPrint.aspx?gameid=175948]

(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  [https://www.truenorthhockey.com/asp_pages/tnhcGameSheetPrint.aspx?gameid=175948]

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

Rename Table @13 to [Mamalukes_Score_Summary];