Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Bart_Breekveldt
New Contributor

How to use IF function in the same column

Dear community,

We have sensors at productionlines which measure the number of products passing by. Every noon they reset the total number for the day. Every minute it gives the new total for the day so far i.e. 14:01:00 = 100654, 14:02:00 = 100676. Therefore I can't use the SUM-function in analysis and I'm using the MAX-function. The problem with this, I can't zoom in to hours or minutes and can't zoom out for weeks, months and further (i.e. It will give the MAX value for the week, when it has already been reset six times at least)

Can I use a combined MAX and SUM function in the Analysis part?  Or should I make a new column in the table which calculates the difference between two measures from the sensors? (in my mind this is the simplest way) I thought about the IF function like MS Excel: 

=IF(condition;if_value_is_true;if_value_is_false)

=IF(measureB1>measureB2;0;measureB2-measureB1)

This leaves the negative values out (when the systems resets).

Which is the simplest way to do a correct measure and how to calculate it?

Thanks in advance.

Labels (1)
2 Solutions

Accepted Solutions
Vegar
Valued Contributor III

Re: How to use IF function in the same column

Have you considered calculating relative amounts in your data. for every row calculate the delta from the previous row within the reset cycle.  

You can get the idea by examening the script below.  

//Assuming dates in format 'YYYY-MM-DD'
RawData: LOAD *,
DayName(Date+Time,-1,0.5) as LogDate , //The file resets every day at noon
frac( Time + 0.5 ) as LogRelativeTime //Time passed from noon Inline [ Date, Time, Measure 2019-04-15, 14:02:00, 100676 2019-04-15, 14:01:00, 100654 2019-04-15, 11:02:00, 200676 2019-04-15, 11:01:00, 200654 2019-04-14, 14:02:00, 100677 2019-04-14, 14:01:00, 100653 2019-04-14, 12:00:00, 3 2019-04-14, 11:02:00, 200677 2019-04-14, 11:01:00, 200653 2019-04-13, 14:02:00, 100678 2019-04-13, 14:01:00, 100656 ]; LOAD LogDate, Date, Time, Measure as [Day total acc Measure], //Your old value if(peek('LogDate')=LogDate, Measure - Peek('Day total acc Measure'), Measure) as Measure Resident RawData ORDER BY LogDate asc, LogRelativeTime asc; Drop table RawData; exit script
Please ekskuse my Norglish and Swenglish typos.
Bart_Breekveldt
New Contributor

Re: How to use IF function in the same column

I get an error while trying to change "measure" to my own column. I want to do it for all eight measures (Dutch: 'metingen'). Have I made an mistake? I've also added the coding for the original table. 

Qlik coding.PNG

Qlik coding.PNG

 

6 Replies
Vegar
Valued Contributor III

Re: How to use IF function in the same column

Have you considered calculating relative amounts in your data. for every row calculate the delta from the previous row within the reset cycle.  

You can get the idea by examening the script below.  

//Assuming dates in format 'YYYY-MM-DD'
RawData: LOAD *,
DayName(Date+Time,-1,0.5) as LogDate , //The file resets every day at noon
frac( Time + 0.5 ) as LogRelativeTime //Time passed from noon Inline [ Date, Time, Measure 2019-04-15, 14:02:00, 100676 2019-04-15, 14:01:00, 100654 2019-04-15, 11:02:00, 200676 2019-04-15, 11:01:00, 200654 2019-04-14, 14:02:00, 100677 2019-04-14, 14:01:00, 100653 2019-04-14, 12:00:00, 3 2019-04-14, 11:02:00, 200677 2019-04-14, 11:01:00, 200653 2019-04-13, 14:02:00, 100678 2019-04-13, 14:01:00, 100656 ]; LOAD LogDate, Date, Time, Measure as [Day total acc Measure], //Your old value if(peek('LogDate')=LogDate, Measure - Peek('Day total acc Measure'), Measure) as Measure Resident RawData ORDER BY LogDate asc, LogRelativeTime asc; Drop table RawData; exit script
Please ekskuse my Norglish and Swenglish typos.
Bart_Breekveldt
New Contributor

Re: How to use IF function in the same column

I get an error while trying to change "measure" to my own column. I want to do it for all eight measures (Dutch: 'metingen'). Have I made an mistake? I've also added the coding for the original table. 

Qlik coding.PNG

Qlik coding.PNG

 

Bart_Breekveldt
New Contributor

Re: How to use IF function in the same column

Qlik coding II.PNG

Vegar
Valued Contributor III

Re: How to use IF function in the same column

image.png

You are missing a comma (,)  after your [Datum-Tijd]

Please ekskuse my Norglish and Swenglish typos.
Bart_Breekveldt
New Contributor

Re: How to use IF function in the same column

Qlik coding III.PNG

(Datum-Tijd translates to Date-Time). Almost there! The function "drop table" doesn't work. Do you know where I went wrong this time?

Bart_Breekveldt
New Contributor

Re: How to use IF function in the same column

I've solved it with the IF & previous combination and Dayname & frac seperately. Try these. Thanks.