3 Replies Latest reply: Jun 29, 2015 4:11 PM by Massimo Grossi RSS

    only load certain rows

    sam brierley

       

      Hi all,

       

      dead quick one...

       

      is there a way to only load in certain value i.e with the if statement if the conditions aren't met a null is created but I just don't want to load it in at all.

       

      Load
      [Vehicle type],
      [Euro standard],
      [Average Speed (km/h)] as [Speed(Lane 1)],
      if([Vehicle type] = 'Artic' and [Euro standard] = 'Euro VI',avg([NOx EF (g/km)])) as [Artic 6 Emissions]

      FROM
      [C:\Users\samuel.brierley\Desktop\TSB AEC\S-Paramics\Feasibility Study\M6 Emissions.xlsm]
      (
      ooxml, embedded labels, table is HGV) group by [Vehicle type], [Euro standard], [Average Speed (km/h)];

       

       

      thanks all

        • Re: only load certain rows
          Michael Tarallo

          Hello Samuel

           

          Check this out and let us know how you do and if you need more assistance:

           

          http://help.qlik.com/sense/2.0/en-US/online/#../Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Load.htm?Highli…

           

          "where is a clause used for stating whether a record should be included in the selection or not. The selection is included if criterion is True.

          criterion is a logical expression."

           

           

          Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

           

          Regards,

          Mike Tarallo

          Qlik

          • Re: only load certain rows
            Sunny Talwar

            Try this:

             

            Load [Vehicle type],
                     [Euro standard],
                     [Average Speed (km/h)] as [Speed(Lane 1)],
                    Avg([NOx EF (g/km)]) as [Artic 6 Emissions]
            FROM
            [C:\Users\samuel.brierley\Desktop\TSB AEC\S-Paramics\Feasibility Study\M6 Emissions.xlsm]
            (ooxml, embedded labels, table is HGV)

            Where Match([Vehicle type], 'Artic', 'Euro VI')

            Group By [Vehicle type], [Euro standard], [Average Speed (km/h)];

            • Re: only load certain rows
              Massimo Grossi

              I think just a little change, move the if in the where part

               

              Load

              [Vehicle type],

              [Euro standard],

              [Average Speed (km/h)] as [Speed(Lane 1)],

              // replace this

              // if([Vehicle type] = 'Artic' and [Euro standard] = 'Euro VI',avg([NOx EF (g/km)])) as [Artic 6 Emissions]

              // with

              avg([NOx EF (g/km)]) as [Artic 6 Emissions]

              FROM

              [C:\Users\samuel.brierley\Desktop\TSB AEC\S-Paramics\Feasibility Study\M6 Emissions.xlsm]

              (ooxml, embedded labels, table is HGV)

              // and add the filter in where

              where [Vehicle type] = 'Artic' and [Euro standard] = 'Euro VI'

              group by [Vehicle type], [Euro standard], [Average Speed (km/h)];