0 Replies Latest reply: Aug 24, 2016 12:12 PM by Tyler Ford RSS

    How to count occurrences of an item in a linked field.

    Tyler Ford

      Hello,

       

      So I'm having an issue. What I have is a list of Port Codes that are tied to a geopoint and geoname so that they can be displayed properly on a map. The four digit port code is a field [Port] with [Location] containing the geopoint data and [Port Name] containing the geoname data. What I'm trying to do is define these ports and their locations/names globally so that I can reference them in my Qlik Sheets and count how many times each port shows up in various excel sheets.

       

      In my Main section in the Load Editor I have this:

      [Ports]:

      LOAD

          [Port],

          [Location],

          [Port Name]

      FROM [lib://Downloads/Ports.xls]

      (biff, embedded labels, table is Sheet1$);

       

      tag fields [Location] WITH $geopoint;

      tag fields [Port Name] WITH $geoname;

       

      Now I have multiple excel files with [Port] as a field as well but when I try and use Count(Port) or Sum(Port) it either counts the number of times the port code shows up in the Ports.xls doc which is only the reference sheet, or adds the port codes together for some non-useful information.

       

      Is there a way to globally define these locations but then count the occurrences of each in a different excel sheet?

       

      Thanks.