topic Script function question in QlikView

Script function question

Tue, 23 Dec 2014 21:49:15 GMT

Hello,

I would like to do something equivalent to the following in SQL for my script in QlikView:

AccountPrescriberRelationship:

LOAD

%AffiliatedEntityTerritoryId as '%EntityTerritoryId',

%EntityTerritoryId as 'Prescriber Id',

Name as 'Prescriber Name',

ZoneTerr as '%PrescZoneTerr'

RESIDENT Table

WHERE %EntityTypeId = 1 and NOT isNull(%AffiliatedEntityTerritoryId) and %PrescZoneTerr = ZoneTerr

and SUBSTRING([%EntityTerritoryId],0,CHARINDEX('-',[%EntityTerritoryId])) =

SUBSTRING(%AffiliatedEntityTerritoryId,0,CHARINDEX('-',%AffiliatedEntityTerritoryId));

So really i am looking for substitutes for the SUBSTRING and CHARINDEX functions.

What I am trying to do is match two parts of two different fields in my WHERE clause:

EntityTerritoryId

6X130206-15040

AffiliatedEntityTerritoryId

6X130206-10222727

Re: Script function question

alexantone — Tue, 23 Dec 2014 23:57:29 GMT

Hello conor cliffe

I think you must modify your table of your database BEFORE include in qlikview

you can create a new field (with substring,and char index) in this table

it' s better to have a clean database for qlikview

Re: Script function question

anbu1984 — Wed, 24 Dec 2014 01:52:30 GMT

Use Subfield()

Subfield([%EntityTerritoryId],'-',1) =Subfield(%AffiliatedEntityTerritoryId,'-',1)

Re: Script function question

jagan — Wed, 24 Dec 2014 02:25:24 GMT

Hi,

Try like this using SubField() or Mid() and Index()

AccountPrescriberRelationship:

LOAD

[%AffiliatedEntityTerritoryId] as [%EntityTerritoryId],

'%EntityTerritoryId] as [Prescriber Id],

Name as [Prescriber Name],

ZoneTerr as [%PrescZoneTerr]

RESIDENT Table

WHERE [%EntityTypeId] = 1 and NOT isNull([%AffiliatedEntityTerritoryId]) and %PrescZoneTerr = ZoneTerr

and SubField([%EntityTerritoryId], '-', 1) =

SubField([%AffiliatedEntityTerritoryId],'-', 1)

AccountPrescriberRelationship:

LOAD

[%AffiliatedEntityTerritoryId] as [%EntityTerritoryId],

'%EntityTerritoryId] as [Prescriber Id],

Name as [Prescriber Name],

ZoneTerr as [%PrescZoneTerr]

RESIDENT Table

WHERE [%EntityTypeId] = 1 and NOT isNull([%AffiliatedEntityTerritoryId]) and %PrescZoneTerr = ZoneTerr

AND Mid([%AffiliatedEntityTerritoryId], 1, Index([%AffiliatedEntityTerritoryId], '-') -1) = Mid([%EntityTerritoryId], 1, Index([%EntityTerritoryId], '-') -1)

Hope this helps you.

Regards,

Jagan.