topic Re: Loading data from html table - problem with br tag in QlikView

Loading data from html table - problem with br tag

mfchmielowski — Tue, 14 Mar 2017 06:34:01 GMT

Hello all.

I'm fetching data from url The Denied Persons List‌ There is a table with column: Name and address.

Name is devided from address with html br tag. When qlikview is parsing the value in this cell it's giving name and address concatenated and i've no idea how to split this two values. I need to have name and address in two different columns in qlikview.

I've tried subfield([Name and addres], chr(10), 1) and subfield([Name and addres], chr(13), 1) but none of this worked.

My load script:

DeniedPersonsTmp:

LOAD [Name and Address],

[Effective Date],

[Expiration Date],

[Type of Denial],

F5,

RowNo() bitand 1 as rowNoBitAnd

FROM

[https://www.bis.doc.gov/dpl/public/dpl.php]

(html, utf8, embedded labels, table is @1);

NoConcatenate

DeniedPersons:

load

, dpEffectiveDateYear&'-'&dpEffectiveDateMonth&'-'&dpEffectiveDateDay as dpEffectiveDateNormal

, if(dpExpirationDateRaw <> '', dpExpirationDateYear&'-'&dpExpirationDateMonth&'-'&dpExpirationDateDay) as dpExpirationDate

, trim(SubField(dpNameAndAddress, ',', 1)) as dpName

, trim(SubField(dpNameAndAddress, ',', 2)) as dpAddress

, trim(SubField(dpNameAndAddress, ',', 3)) as dpAddress2

;

load

'bis.doc.gov' as dpDataSource,

[Name and Address] as dpNameAndAddress,

SubField([Effective Date], '/', 3) as dpEffectiveDateYear,

SubField([Effective Date], '/', 2) as dpEffectiveDateDay,

SubField([Effective Date], '/', 1) as dpEffectiveDateMonth,

SubField([Expiration Date], '/', 3) as dpExpirationDateYear,

SubField([Expiration Date], '/', 2) as dpExpirationDateDay,

SubField([Expiration Date], '/', 1) as dpExpirationDateMonth,

[Expiration Date] as dpExpirationDateRaw,

[Type of Denial] as dpTypeOfDenial

Resident DeniedPersonsTmp Where rowNoBitAnd = 1;

drop table DeniedPersonsTmp;

drop fields dpExpirationDateRaw from DeniedPersons;

Re: Loading data from html table - problem with br tag

marcus_sommer — Tue, 14 Mar 2017 11:59:33 GMT

Try it with: subfield([Name and addres], chr(13) & chr(10), 1)

- Marcus

Re: Loading data from html table - problem with br tag

mfchmielowski — Tue, 14 Mar 2017 12:20:16 GMT

I've tried that already.

DeniedPersonsTmp:

LOAD

SubField([Name and Address], ',', 1) as dpName,

index([Name and Address], chr(13)) as isChr13Raw,

index([Name and Address], chr(10)) as isChr10Raw,

FROM

[https://www.bis.doc.gov/dpl/public/dpl.php]

(html, utf8, embedded labels, table is @1);

Looks like qlikview internal html parser makes something like replace(cellValue, '<br>', '')

Re: Loading data from html table - problem with br tag

rwunderlich — Wed, 15 Mar 2017 04:51:16 GMT

I think you've run up against a limitation of the QV HTML loader. Each <td> tag is treated as a field, but tags within the <td> are stripped, giving you no way to separate Name and Address. However, a nice csv version of the same data is available here:

https://www.bis.doc.gov/dpl/dpl.txt

That file offers a nice breakout of the individual attributes.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Loading data from html table - problem with br tag

mfchmielowski — Wed, 15 Mar 2017 06:01:38 GMT

Thank You very much