Hello,
I am new to development and Qlikview. I am trying to create an application that uploads news from different RSS feeds. I have created the database but I would like to have a view with the most recent published titles per news channel (currently I have 4 channels). This view could be available on the Homepage or activated at the push of a button. Each article should show up in a text box; basically I would have a text box with the news channel logo and on the right-hand side of the logo, I would have 3 text boxes, each containing one of the 3 most recent topics published by that channel.
The database is created in the following manner:
1. Load data per channel:
Fact_tmp:
LOAD
'Digi24' as Source, // I create this field to differentiate each channel
title,
link,
pubDate as PublicationDate, // example of initial format: "Wed, 03 May 2017 12:22:30 +0300" and I create PublicationDate and PublicationTime fields in the second step
description,
[enclosure/url] as url
FROM [http://www.digi24.ro/rss] (XmlSimple, Table is [rss/channel/item]);
CONCATENATE
LOAD
'HotNews' as Source,
title,
link,
description,
pubDate as PublicationDate
FROM [http://www.hotnews.ro/rss] (XmlSimple, Table is [rss/channel/item]);
.... same for the remaining channels
2. Transform data and create final Fact table:
Fact:
LOAD
Source,
title,
link,
description,
Date(Date#(trim(PurgeChar(TextBetween(PublicationDate,',',SubField(PublicationDate,' ',5)),',')),'DD MMM YYYY'),'DD MMM YYYY') as PublicationDate,
time(SubField(PublicationDate,' ',5)) as PublicationTime,
url
Resident Fact_tmp;
DROP Table Fact_tmp;
Trials so far:
1. In script:
>> This solution returns most recent title but because I used firstsortedvalue (just found out about this option), I only get 1 title per source:
Load
firstsortedvalue(title,PublicationTime) as rcentTitle,
Date(max(PublicationDate),'DD MMM YYYY') as recentDate,
Source as recentSource
Resident Fact
Group by Source
Order by PublicationDate desc, PublicationTime desc;
>> This solution returns top 3 recent titles, but not by source:
first 3
Load
title,
PublicationTime as rcentTitle,
concat(PublicationDate,' ') as recentDate,
Source as recentSource
Resident Fact
Group by Source, PublicationDate, PublicationTime, title
Order by PublicationDate desc, PublicationTime desc;
2. In UI:
>> This is the best solution I have but if I want to add more sources, then I would have to create variables per each channel and I would like to know if there is an easier way of achieving the same result:
-> vLatestDate: =date(max({<PublicationDate= , PublicationTime= , title= , description=,Source={'TVR1'} >}PublicationDate),'DD MMM YYYY')
-> vLatestTime1: =time(max({1<PublicationDate={"$(vLatestDate)"},Source={'TVR1'}>}PublicationTime,1))
-> vLatestTime2: =time(max({1<PublicationDate={"$(vLatestDate)"},Source={'TVR1'}>}PublicationTime,2))
-> vLatestTime3: =time(max({1<PublicationDate={"$(vLatestDate)"},Source={'TVR1'}>}PublicationTime,3))
Please consider that this is more of a curiosity, not urgent at all, still, if you have any ideas on this topic, I would appreciate your point of view.
Thank you in advance,
Diana
