Updating a field in a row

martynwb — Fri, 17 Dec 2021 19:05:38 GMT

Hi,

I need to update a row in a table based on a lookup value. I've already go the lookup value and I'm just looking for the best way to update the field.

I have this Host table.

Host:
LOAD
ID As QualysHostID,
IP As QualysHostIP,
TRACKING_METHOD As QualysHostTrckingMethod,
OS As QualysHostOS,
LAST_SCAN_DATETIME As QualysHostLastScanDateTime,
LAST_VM_SCANNED_DATE As QualysHostLastVMScanDate,
LAST_VM_SCANNED_DURATION As QualysHostLastVMScanDur,
DNS As QualysHostDNS,
OS_CPE As QualysHostOSCPE,
NETBIOS As QualysHostNetBios,
QG_HOSTID As QualysHostQGHostID,
LAST_VM_AUTH_SCANNED_DATE As QualysHostLastVMAuthScanDate,
LAST_VM_AUTH_SCANNED_DURATION As QualysHostLastVMAuthScannedDur,
"DNS_DATA/HOSTNAME" As QualysHostHostname,
"DNS_DATA/DOMAIN" As QualysHostDomain,
"DNS_DATA/FQDN" As QualysHostFQDN,
%Key_HOST_LIST_VM_DETECTION_OUTPUT_087173C108D6BD52,
%Key_HOST_312E40B6043C9FFE,
'' as SubDivision
FROM [lib://Qualys/Newdetection_*withTag.xml]
(XmlSimple, table is [HOST_LIST_VM_DETECTION_OUTPUT/RESPONSE/HOST_LIST/HOST]);

You'll note that I added an empty column to the load, which I'm expecting to populate having looked up the relevant value.

So, I now have a QualysHostID value and a value to place in the 'SubDivision' column. How can I do that most efficiently? It will operate within a loop and all Host entries will eventually be assigned a 'SubDivision' based on a reference table (that logic appears to be working already).

Your help is appreciated!

Re: Updating a field in a row

maxgro — Fri, 17 Dec 2021 21:01:05 GMT

I think you can use a mapping table (lookup table) and ApplyMap function

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/MappingFunctions/ApplyMap.htm

Re: Updating a field in a row

martynwb — Sat, 18 Dec 2021 17:08:18 GMT

Hi and thanks

So, would you advise that I build the mapping table and add one line at a time (concatenate) until I've looped through all values, then I applya map from the mapping table to the original qvd?

Martyn

topic Re: Updating a field in a row in App Development

Updating a field in a row

Re: Updating a field in a row

Re: Updating a field in a row