Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

k_hamson
New Contributor II

Setting Conditions in Qlik Sense

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
1 Solution

Accepted Solutions
MVP
MVP

Re: Setting Conditions in Qlik Sense

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

4 Replies
MVP
MVP

Re: Setting Conditions in Qlik Sense

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

;

MVP
MVP

Re: Setting Conditions in Qlik Sense

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

k_hamson
New Contributor II

Re: Setting Conditions in Qlik Sense

This works exactly as I was wanting! I chose the table option as I use the same data in other areas of my app that I want to display in a different manor.

This is what it looks like prior to taking zero values out:

And this is how it displays after:

Thank you for the very detailed easy to follow solution!

MVP
MVP

Re: Setting Conditions in Qlik Sense

You're welcome

Could you also please close the thread as "answered" by tagging the correct answer as "correct"?

Community Browser