Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
For reformatting the date try date#()
Maybe
DATE (Date#(trim (fieldname),'WWWW MMM DD, YYYY'),'MM/DD/YYYY')
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];
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];
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...
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];