Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Bart_Breekveldt
Contributor III
Contributor III

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
MVP
MVP

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

View solution in original post

Bart_Breekveldt
Contributor III
Contributor III
Author

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

 

View solution in original post

6 Replies
Vegar
MVP
MVP

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
Bart_Breekveldt
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Qlik coding II.PNG

Vegar
MVP
MVP

image.png

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

Bart_Breekveldt
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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