5 Replies Latest reply: Jun 1, 2017 7:04 AM by Sunny Talwar RSS

    Find Match Value

    Dipesh Vadgama

      Hi All,

       

      I am trying to find match value from "Match Name" list in "Full Name" List. If its match then I need output Match Name.

      I use index and match formula in excel, but I am unable to figure it out in Qlikview.

        • Re: Find Match Value
          Sunny Talwar

          May be this

           

          MappingTable:

          Mapping

          LOAD [Match Name],

            '@' & [Match Name] & '%'

          FROM

          [..\..\Downloads\Match.xlsx]

          (ooxml, embedded labels, table is [Match Name]);

           

          Table:

          LOAD [Full Name],

              TextBetween(MapSubString('MappingTable', [Full Name]), '@', '%') as [Need Output Here]

          FROM

          [..\..\Downloads\Match.xlsx]

          (ooxml, embedded labels, table is [Full Name]);

            • Re: Find Match Value
              Dipesh Vadgama

              Thanks Sunny,

               

              This works, If possible can you explain me how this works. Why @ & %, Can't I apply direct Mapsubstring

                • Re: Find Match Value
                  Sunny Talwar

                  Without Text Between, you will just replace the specific text with a new value (or same value in your case) Test the below out.

                   

                  MappingTable:

                  Mapping

                  LOAD [Match Name],

                    [Match Name]

                  FROM

                  [..\..\Downloads\Match.xlsx]

                  (ooxml, embedded labels, table is [Match Name]);

                   

                  Table:

                  LOAD [Full Name],

                       MapSubString('MappingTable', [Full Name]) as [Need Output Here]

                  FROM

                  [..\..\Downloads\Match.xlsx]

                  (ooxml, embedded labels, table is [Full Name]);

                   

                  The idea is to add a unique character in the front and end of the text you want to see and then use Text between function to pull the specific text out of the whole thing

                    • Re: Find Match Value
                      Dipesh Vadgama

                      Thank Sunny,

                       

                      Very helpful. I have one more question.

                      How to find match value from "Match Name" list in "Full Name" List. If its match then I need output Match Code value but not the match name this time.

                       

                      I have attached the file for example.

                        • Re: Find Match Value
                          Sunny Talwar

                          Try this

                           

                          MappingTable:

                          Mapping

                          LOAD [Match Name],

                            '@' & Code & '%'

                          FROM

                          [..\..\Downloads\Match (1).xlsx]

                          (ooxml, embedded labels, table is [Match Name]);

                           

                          Table:

                          LOAD [Full Name],

                              TextBetween(MapSubString('MappingTable', [Full Name]), '@', '%') as [Need Output Here]

                          FROM

                          [..\..\Downloads\Match.xlsx]

                          (ooxml, embedded labels, table is [Full Name]);

                           

                          Capture.PNG