topic Re: SQL Select Script - Replace field value with wildcard search in QlikView

SQL Select Script - Replace field value with wildcard search

nigelapt — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone

I just got Qlikview this week. Apologies if I am asking a question which has already been answered. I have been searching the discussion board all day, trying various suggestions but have been unable to find a solution to my problem.

I have a script setup to retrieve data from two SQL database tables and join the results into one table. The tables just contains 5 fields - DEVICE_NAME, INSTALLED, AGENT_VER, ROLE, MGMT_GRP.

The DEVICE_NAME is a fully qualified domain name for servers located in different regions. In order to be able to use the DEVICE_NAME to make relationships with other tables, I need to strip the domain part of the server name eg

servereurope1.eur.nsroot.net (I need to remove .eur.nsroot.net)

serverusa1.nam.nsroot.net (I need to remove .nam.nsroot.net)

The domain is always the suffix, but the server names vary in length.

This is my script which is successfully connecting and retrieving the data into one table:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OperationsManager_CTX;Data Source=SCMOPSWDCSQL2V,2431;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LDNXXX26;Use Encryption for Data=False;Tag with column collation when possible=False];

SCOM_ServerInventory:
SQL SELECT DisplayName as DEVICE_NAME, AgentInstalled as INSTALLED, AgentVersion as AGENT_VER, 'StoreFront Server Computer Role' as ROLE, 'SCOMNAM12_CTX' as MGMT_GRP
FROM dbo.MTV_ComTrade$Citrix$StoreFront$Server$ComputerRole
ORDER BY DisplayName;

concatenate

SQL SELECT DisplayName as DEVICE_NAME, AgentInstalled AS INSTALLED, AgentVersion AS AGENT_VER, 'Provisioning Services Computer Role' as ROLE, 'SCOMNAM12_CTX' as MGMT_GRP
FROM dbo.MTV_Comtrade$Citrix$ProvisioningServices$Server$ComputerRole
ORDER BY DisplayName;

Any advice would be gratefully to strip the domain part from the server name during the selection.

Thanks

Nigel

Re: SQL Select Script - Replace field value with wildcard search

alexdataiq — Mon, 23 May 2016 22:34:10 GMT

Well, you could do it in a preceding Load:

SCOM_ServerInventory:
LOAD
    Left(DEVICE_NAME, Index(DEVICE_NAME, '.') -1) as DEVICE_NAME,
    INSTALLED,
    AGENT_VER,
    ROLE,
    MGMT_GRP;
SQL SELECT DisplayName as DEVICE_NAME, AgentInstalled as INSTALLED, AgentVersion as AGENT_VER, 'StoreFront Server Computer Role' as ROLE, 'SCOMNAM12_CTX' as MGMT_GRP
FROM dbo.MTV_ComTrade$Citrix$StoreFront$Server$ComputerRole
ORDER BY DisplayName;

That will get you the server name and will discard the suffix.

Cheers.

Re: SQL Select Script - Replace field value with wildcard search

MarcoWedel — Mon, 23 May 2016 23:00:37 GMT

Hi,

use a preceding load like this:

LOAD SubField(DEVICE_NAME,'.',1) as DEVICE_NAME,

INSTALLED,

AGENT_VER,

ROLE,

MGMT_GRP;

SQL Select

...

FROM

...

hope this helps

regards

Marco

Re: SQL Select Script - Replace field value with wildcard search

nigelapt — Tue, 24 May 2016 08:05:45 GMT

Hi Alejandro, thank you for taking time to reply and providing a solution to my problem.

I am really grateful, as I spent hours yesterday trying to get this to work.

Thanks again.

Nigel

Re: SQL Select Script - Replace field value with wildcard search

nigelapt — Tue, 24 May 2016 08:07:08 GMT

Hi Marco,

Thank you for helping me out with this problem.

I received two perfect solutions within a matter of hours.

Really impressed with the product and the community.

Thanks

Nigel

Re: SQL Select Script - Replace field value with wildcard search

MarcoWedel — Tue, 24 May 2016 20:43:23 GMT

Hi Nigel,

I guess that's what this community is all about.

Should be difficult to find a better combination

regards

Marco