3 Replies Latest reply: Dec 23, 2014 9:25 PM by jagan mohan rao appala RSS

    Script function question

      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
          Alex Pigeard

          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
            anbu cheliyan

            Use Subfield()

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

            • Re: Script function question
              jagan mohan rao appala

              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.