Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

How to extract date in a Web Page in QlikSense

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

1 Solution

Accepted Solutions
vinieme12
Not applicable

Re: How to extract date in a Web Page in QlikSense

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;

6 Replies
vinieme12
Not applicable

Re: How to extract date in a Web Page in QlikSense

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
Not applicable

Re: How to extract date in a Web Page in QlikSense

For reformatting the date try date#()

Maybe

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

Not applicable

Re: How to extract date in a Web Page in QlikSense

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
Not applicable

Re: How to extract date in a Web Page in QlikSense

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

Not applicable

Re: How to extract date in a Web Page in QlikSense

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
Not applicable

Re: How to extract date in a Web Page in QlikSense

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

fromfield2.JPG

fromfield.JPG