Script function question

[#1]

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

[alexantone]

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

[anbu1984]

Use Subfield()

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

[jagan]

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)

OR

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.