4 Replies Latest reply: Aug 8, 2018 9:42 AM by Petter Skjolden RSS

    Setting Conditions in Qlik Sense

    Kayla Hamson

      Hello,

      I am using Qlik Sense and trying to come up with a solution for the following:

      Create a graph or table (to me this does not matter) based on repeat items in the last 7 days.

      What I have is a file that is generated with machines ("Press") "Prob code text, Cause code text, and dates through excel and is uploaded using the Data Load Editor.

       

      What I would like is if the date is = today thru 7days ago and the Press, Prob code text and Cause code text are the same then this is shown.

      Is there anyway to code these types of conditions into the graph or table.

       

      Here is some examples:

      Example 1 would be shown.

      Example 2 the Cause code is not the same - no show

      Example 3 the date is more than 7 days apart - no show

      Example 4 the Prob code text is not the same - no show

      Example 5 the Press is not the same - no show

         

      ExampleMalfunct startPressProb code textCause code text
      18/1/2018 CP088LoaderFitting
      8/5/2018 CP088LoaderFitting
      28/1/2018CP122Leak - AirValve - Solenoid
      8/5/2018CP122Leak - AirAir Leak
      37/30/2018 CP089Leak - AirValve - Solenoid
      8/7/2018 CP089Leak - AirValve - Solenoid
      48/1/2018 CP088Center MechFitting
      8/5/2018 CP088LoaderFitting
      58/1/2018 CP069Center MechFitting
      8/5/2018 CP072Center MechFitting
        • Re: Setting Conditions in Qlik Sense
          Petter Skjolden

          You can filter out the matching rows in the load script by doing this:

          This script leaves you with a single row containing both the dates instead of two rows...

           

          SET DateFormat='MM/DD/YYYY';
          
          
          DATA:
          LOAD
            *,
            Previous([Malfunct start]) AS [Malfunct start 2]
          FROM
            LIB://DATA/ProblemCodes.xlsx (ooxml........)
          WHERE
                Previous(Press)=Press
            AND Previous([Prob code text])=[Prob code text]
            AND Previous([Cause code text])=[Cause code text]
            AND Today(1)-[Malfunct start]<=7
            AND Today(1)-Previous([Malfunct start])<=7
          ;
          


           

          If you need to have two nearly identical rows with just the differing dates you can add a preceding load to split them into two rows from one row:

           

          SET DateFormat='MM/DD/YYYY';
          
          
          DATA:
          LOAD
            If( IterNo() = 1 , [Malfunct start 2] , [Malfunct start] ) AS [Malfunct start],
            Press,
            [Prob code text],
            [Cause code text]
          WHILE
            IterNo()<=2
          ;
          LOAD
            *,
            Previous([Malfunct start]) AS [Malfunct start 2]
          FROM
            LIB://DATA/ProblemCodes.xlsx (ooxml........)
          WHERE
                Previous(Press)=Press
            AND Previous([Prob code text])=[Prob code text]
            AND Previous([Cause code text])=[Cause code text]
            AND Today(1)-[Malfunct start]<=7
            AND Today(1)-Previous([Malfunct start])<=7
          ;
          
          • Re: Setting Conditions in Qlik Sense
            Petter Skjolden

            If you prefer to do the filtering directly in the chart and load the entire table into Qlik Sense first then the table in a sheet can use this expression in a measure and label it STATUS:

             

            If(  (Above(Press)=Press

              AND Above([Prob code text])=[Prob code text]

              AND Above([Cause code text])=[Cause code text]

              AND Today(1)-[Malfunct start]<=7

              AND Today(1)-Above([Malfunct start])<=7)

              OR (

                Below(Press)=Press

              AND Below([Prob code text])=[Prob code text]

              AND Below([Cause code text])=[Cause code text]

              AND Today(1)-[Malfunct start]<=7

              AND Today(1)-Below([Malfunct start])<=7

              )

              , 'Repeat within the last 7 days' )

             

            Add all the fields as DIMENSIONS. Make sure that the SORTING leaves the [Malfunct start] at the end of the sorting list in the properties of the table. Also go to "Add-ons" and remove the checkmark on the "Include zero values" so you are left with only the rows that have 'Repeat within the last 7 days' and discarding the ones with NULL values.

             

            2018-08-08 00_04_12-Settings.png