Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
kaushi2020
Creator III
Creator III
‎2024-11-06 07:12 AM

How to convert tags to columns with data from html xmlns

Dear All, I have a file where we are getting a column named description which have value like below. I wanted to know if there a way i can get the tag names and put in a column ? 

and the value as data ?

<html xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:msxsl="urn:schemas-microsoft-com:xslt">

<head>

<META http-equiv="Content-Type" content="text/html">

<meta http-equiv="content-type" content="text/html; charset=UTF-8">

</head>

<body style="margin:0px 0px 0px 0px;overflow:auto;background:#FFFFFF;">

<table style="font-family:Arial,Verdana,Times;font-size:12px;text-align:left;width:100%;border-collapse:collapse;padding:3px 3px 3px 3px">

<tr style="text-align:center;font-weight:bold;background:#9CBCE2">

<td>Daba</td>

</tr>

<tr>

<td>

<table style="font-family:Arial,Verdana,Times;font-size:12px;text-align:left;width:100%;border-spacing:0px; padding:3px 3px 3px 3px">

<tr>

<td>Start_ID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>End_ID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>OWNER_CD</td>

<td>PAEW</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>LIFE_CYCLE_STATUS</td>

<td>Active</td>

</tr>

<tr>

<td>INSTALL_DATE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>ENABLED</td>

<td>True</td>

</tr>

<tr>

<td>SUBTYPE_CD</td>

<td>Distribution Mains</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>LEGACY_ID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>WATER_TYPE</td>

<td>Potable</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>DEPTH</td>

<td>1.1</td>

</tr>

<tr>

<td>DIAMETER</td>

<td>100 mm</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>MANUFACTURER_CODE</td>

<td>Amiantit Oman</td>

</tr>

<tr>

<td>MATERIAL</td>

<td>High Density Poly Pipe</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>LINING_TYPE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>EXTERIOR_COATING</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>DEAD_END_INDICATOR</td>

<td>No</td>

</tr>

<tr>

<td>GROUND_SURFACE_TYPE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>INSULATED_INDICATOR</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>PIPE_CLASS</td>

<td>pn16 pe100</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>Pinchable</td>

<td>No</td>

</tr>

<tr>

<td>ROUGHNESS</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>HW_COEFFICIENT</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>C_FACTOR</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>MEASURED_LENGTH</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>MAX_OPERATING_PRESSURE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>OPERATING_PRESSURE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>FLOW_RATE</td>

<td>30</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>PRESSURE_CD</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>PRESSURE_ZONE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>CLEAN_METHOD</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>CLEAN_DATE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>CONDITION</td>

<td>Good</td>

</tr>

<tr>

<td>CONDITION_NOTE</td>

<td>1</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>CONDITION_DATE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>Street_Address</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>WILAYAT_NAME</td>

<td>Daba</td>

</tr>

<tr>

<td>REGION_NAME</td>

<td>Musandam Governorate</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>DMA_NAME</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>Pump_Pressure</td>

<td>6</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>Exposed</td>

<td>No</td>

</tr>

<tr>

<td>CREATION_USER</td>

<td>PAEW</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>DATE_CREATED</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>LAST_USER</td>

<td>MOOSA</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>DATE_MODIFIED</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>WATER_TRACE_WEIGHT</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>ORIGINAL_OBJECTID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>CONTRACTORNAME</td>

<td>TARGET LLC</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>CONSULTANTNAME</td>

<td>RENARDET S.A. &amp; PARTNERS</td>

</tr>

<tr>

<td>DRAWINGNUMBER</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>PROJECTTITLE</td>

<td>DABA, AR RAWDAH &amp; LIMA TANDER 32-2012</td>

</tr>

<tr>

<td>Datasource</td>

<td>TARGET LLC</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BatchNumber</td>

<td>32-2012</td>

</tr>

<tr>

<td>filepath</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>REMARKS</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>created_user</td>

<td>PAEW</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>created_date</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>last_edited_user</td>

<td>PAEW</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>last_edited_date</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>Feature_ID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>Maintenance_Date</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>AUTON</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>ASSETID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>GISOBJID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>ORGID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr>

<td>EAM_ASSETCODE</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>CRITICALITY</td>

<td>Moderate (2-3)</td>

</tr>

<tr>

<td>PAEW_ID</td>

<td>&lt;Null&gt;</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>SHAPE</td>

<td>Polyline</td>

</tr>

<tr>

<td>GLOBALID</td>

<td>{E94E3419-3EB8-4DE3-97C1-AFDE2C1E2872}</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>SHAPE.LEN</td>

<td>83.731751</td>

</tr>

</table>

</td>

</tr>

</table>

</body>

</html>

Thanks in advance.

243 Views
5 Replies
Kushal_Chawda
MVP
MVP
‎2024-11-06 09:20 AM

@kaushi2020  is this HTML always contains one value for one field?

217 Views
0 Likes
kaushi2020
Creator III
Creator III
‎2024-11-06 11:38 PM
Author

here this html will have a predefined set of tags. output is as below. 

kaushi2020_0-1730954124359.png

if there is any way we can put all these values in the left side in a column and their attributes in another column we can use this in qlik sense further.

 thanks again for your reply.

 

200 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2024-11-07 04:46 AM

@kaushi2020  try below

Data:
LOAD @1,
     @2 
FROM [lib://files/test.html]
(html, codepage is 28591, no labels, table is @1);

Final:
load * Inline [
Junk ];

 for Each vField in FieldValueList('@1')

Join(Final)
Load @2 as [$(vField)]
Resident Data
where @1 ='$(vField)';

Next

Drop Table Data;
182 Views
0 Likes
kaushi2020
Creator III
Creator III
‎2024-11-18 11:38 PM
Author

In response to Kushal_Chawda

Thanks Kushal for your revert, I have attached sample data where you can see how we are getting data. we get this complete tags in rows in the database. 

Preview file
11 KB
125 Views
0 Likes
kaushi2020
Creator III
Creator III
4 weeks ago
Author

Dear All, anyone any luck on this ? 

79 Views
0 Likes