6 Replies Latest reply: Apr 8, 2015 3:56 AM by Lance Chong RSS

    Use of qlikview generated fields

      Hi all,

       

      I have a simple question: I want to use the fields generated by qlikview. The script below illustrates what I have done. In this case, how can I make use of [Custodian Final] further in the script? What I want to do for example is combine the new field with another field by [Custodian Final]&"_"&[Trans. code].

       

      StarMap:

      LOAD [Trans. code],

           [Security type],

           Counterparty,

      Operations,

       

      From

      [C:\...]

       

      LEFT Join (StarMap)

      LOAD Operations,

           [Custodian Final]

       

      From

      [C:\...]

       

       

      Thanks in advance!

        • Re: Use of qlikview generated fields
          Martin Pohl

          Hi,

          for example use mapping / applymap

          Map:

          mapping

          LOAD Operations,

               [Custodian Final]

          From

          [C:\...]

           

          StarMap:

          LOAD [Trans. code],

               [Security type],

               applymap('Map',Operations,'not found')&'_'&[Trans. code] as NewField

               Counterparty,

          Operations,

           

          From

          [C:\...]


          If there are more fields from the second table you have to reload the StarMap table:

          StarMap2:

          load

          *,

          [Custodian Final]&"_"&[Trans. code] as NewField1,

          .... as NewField2 and so on

          Regards

            • Re: Use of qlikview generated fields

              Thank you for the prompt reply.

               

              However, I can't get StarMap2 to display. I am sure I am doing something wrong. Below is the script I am using. It is different from the example I have shown you before. In this example I can derive from the model portfolio or model portfolio code whether something is internal or external. In the end I want to combine the field [Code intern/extern] with the created fields [intern/extern 2] and [intern/extern 3].

               

               

               

              Map:

              mapping

              LOAD [Model portfolio code],

                   [intern/extern 2]

              FROM

              [U:\..;

               

               

              Map2:

              mapping

              LOAD [Model portfolio],

                   [intern/extern 3]

              FROM

              [U:\...);

               

               

              StarMap:

              LOAD [Transaction No.],

                   [Model portfolio],

                   [Model portfolio name],

                   [Model portfolio code],

                   [Code intern/extern],

                   [Reconciliation ID],

                   applymap('Map',[Model portfolio code],'not found')&'_'&[Trans. code] as InternExtern,

                   applymap('Map2',[Model portfolio],'not found')&'_'&[Trans. code] as InternExtern2

              FROM

              [U:\...;

               

              StarMap2:

              load

              *,

              [Code intern/extern]&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

              ;

                • Re: Use of qlikview generated fields
                  Martin Pohl

                  I missed something in my script (and you too)

                   

                  StarMap2:

                  load

                  *,

                  [Code intern/extern]&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

                  resident StarMap

                  ;

                   

                  drop table StarMap;


                  Regards

                    • Re: Use of qlikview generated fields

                      Hi,

                       

                      I get the following error:

                       

                      Field not found - <_>

                      StarMap2:

                      load

                      *,

                      [Code intern/extern]&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

                      resident StarMap

                       

                       

                       

                       

                      My script now looks like the following:

                       

                      Map:

                      mapping

                      LOAD [Model portfolio code],

                           [intern/extern 2]

                      FROM

                      [U:\...);

                       

                       

                      Map2:

                      mapping

                      LOAD [Model portfolio],

                           [intern/extern 3]

                      FROM

                      [U:\...);

                       

                       

                      StarMap:

                      LOAD [Transaction No.],

                           [Trans. code],

                           [Security No.],

                           [Security type],

                           Counterparty,

                           Custodian,

                           [Settlement date],

                           [Changed  by user],

                           [Changed date],

                           [Created by user],

                           [Created date],

                           Portfolio,

                           [Model portfolio],

                           [Model portfolio name],

                           [Model portfolio code],

                           Custody,

                           [Custody account],

                           Currency,

                           Nominal,

                           [Instrument type],

                           [Transaction cancellation flag],

                           [Bank account],

                           Operations,

                           [Code intern/extern],

                           [Reconciliation ID],

                           applymap('Map',[Model portfolio code],'not found')&'_'&[Trans. code] as InternExtern,

                           applymap('Map2',[Model portfolio],'not found')&'_'&[Trans. code] as InternExtern2

                      FROM

                      [U:\...);

                       

                      StarMap2:

                      load

                      *,

                      [Code intern/extern]&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

                      resident StarMap

                      ;

                       

                      drop table StarMap;