topic Re: Need help in extracting a field in QlikView

Need help in extracting a field

Anonymous — Tue, 14 Apr 2015 07:31:29 GMT

I have a table name Device Attributes

I have 2 column in this table sName and sValue

When I brought this table in QlikView, below is the result. So sName and right side is their results. I need Location from sName as a separate field / column. (So I will be having a new column as Location and its result would be Abu Dhabi)...

sName	sValue
Brand	Fortinet, Inc.
Contact	Jim
Description	Dubai A20
Location	Abu Dhabi
MACAddress
MACAddressVendor	Fortinet Inc.
Model
Name
OID
OS
OSVersion
WAN Router	Yes

Re: Need help in extracting a field

PrashantSangle — Tue, 14 Apr 2015 07:40:59 GMT

Hi,

In script

try below

DeviceAttribute:

Load sName as Location,

sValue as LocationName

from DeviceAttribute

where wildmatch(sName,'Location');

Regards

Re: Need help in extracting a field

jagan — Tue, 14 Apr 2015 07:47:55 GMT

Hi Vijay,

Try like this

Data:

LOAD

FROM Data

WHERE sName <> 'Location';

JOIN

LOAD

sValue AS Location

FROM Data

WHERE sName = 'Location';

Regards,

Jagan.

Re: Need help in extracting a field

Anonymous — Tue, 14 Apr 2015 08:27:58 GMT

Hi Max,

I am getting an error as ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'wildmatch' is not a recognized built-in function name. SQL SELECT nDeviceID, sName, sValue FROM WhatsUp.dbo.DeviceAttribute where wildmatch(sName,'Location')

Re: Need help in extracting a field

Anonymous — Tue, 14 Apr 2015 08:28:37 GMT

Hi Jagan,

I dont have a table as Data.. Can you please explain why we are inserting a Data table?

Re: Need help in extracting a field

jagan — Tue, 14 Apr 2015 08:31:44 GMT

Hi,

Rename table name Data with your corresponding table.

Data:

SELECT

FROM YourTableName

WHERE sName <> 'Location';

JOIN

SELECT

sValue AS Location

FROM YourTableName

WHERE sName = 'Location';

Regards,

Jagan.

Re: Need help in extracting a field

Anonymous — Tue, 14 Apr 2015 08:35:26 GMT

Both sName and sValue are from same table.. Why we are joining them in 2 pieces...?

Re: Need help in extracting a field

jagan — Tue, 14 Apr 2015 08:36:37 GMT

Since you want location as a separate column we are doing this.

Regards,

Jagan.

Re: Need help in extracting a field

awhitfield — Tue, 14 Apr 2015 08:40:52 GMT

From you details above, sName is the Name of the field you are populating and sValue is the value that you are adding to the sName Field.

Not sure what your issue actually is, can you post a sample of your data and example .qvw

Andy

Re: Need help in extracting a field

PrashantSangle — Tue, 14 Apr 2015 09:04:19 GMT

Hi,

Wildmatch() is QlikView Function.

If you are comparing your data in SQL query then use like or simply = as suggested by jagan.

Regards

Re: Need help in extracting a field

Anonymous — Tue, 14 Apr 2015 10:51:38 GMT

Thanks Jagan and Thanks everyone. Its working like a feather...