Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nigelapt
Contributor III
Contributor III

SQL Select Script - Replace field value with wildcard search

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

1 Solution

Accepted Solutions
MarcoWedel

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


View solution in original post

5 Replies
alexdataiq
Partner - Creator III
Partner - Creator III

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.

MarcoWedel

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


nigelapt
Contributor III
Contributor III
Author

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

nigelapt
Contributor III
Contributor III
Author

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

MarcoWedel

Hi Nigel,

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

Should be difficult to find a better combination

regards

Marco