Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there experts
I have two tables, one with a work order and one with downtimes like below:
WO:
WOId | Started | Ended |
1 | 2019/01/01 | 2019/01/15 |
2 | 2019/01/05 | 2019/01/10 |
3 | 2019/01/09 | 2019/01/08 |
4 | 2019/01/07 |
Downtime:
WOId | DowntimeType | DowntimeDays |
1 | A | 5 |
1 | B | 1 |
3 | A | 0.5 |
3 | C | 1 |
When the two are concatenated have the following table:
WOId | Started | Ended | DowntimeType | DowntimeDays |
1 | 2019/01/01 | 2019/01/15 | A | 5 |
1 | 2019/01/01 | 2019/01/15 | B | 1 |
2 | 2019/01/05 | 2019/01/10 | ||
3 | 2019/01/09 | 2019/01/08 | A | 0.5 |
3 | 2019/01/09 | 2019/01/08 | C | 1 |
4 | 2019/01/07 |
I want to know if it is possible with set analysis (and hopefully not using IF statements) to do the following:
If there is downtime for a work order (WOId), I want the sum of the downtime. However, if there is not downtime, I want to get the difference between the Ended and Started dates provided the Ended is after the Started.
I got this to work partially by using Alt the following expression:
Sum({<WOId = {'=([Ended] - [Started]) >= 0'}>} Alt(DowntimeDays, Ended - Started))
However, I do not want the set expression's conditions to be taken into account when there is actual downtime (I do not care if the Ended was before the Started if there was actual downtime). Below is the expected output:
WOId | Downtime |
1 | 6 |
2 | 5 |
3 | 1.5 |
4 | 0 |
Can I do this with pure set analysis or should I use an If statement like below (which works)?
If(Sum(DowntimeDays)>0,Sum(DowntimeDays),Sum({<WOId = {'=([Ended] - [Started]) >= 0'}>} Ended - Started))
I try to stay away from IF statements with large datasets for performance reasons after reading this thread: https://community.qlik.com/t5/QlikView-App-Development/If-statement-vs-set-analysis/td-p/591661
My real dataset is obviously a lot bigger and more complex than this example .
Thank you in advance for any help!
Mauritz
Hi Sunny
I got it working with a slight variation:
RangeSum( Sum(DowntimeDays), Sum({<WOId = {"=([Ended] - [Started]) >= 0 and Sum(DowntimeDays) = 0"}>} Ended - Started) )
I guess the Len(Trim()) function did not work because I could have more than one downtime entry for a work order. I did not realise that you could use multiple conditions within a set expression between quotes (although it seems obvious now that you pointed it out).
I will let you know if I can find any change in the performance between your formula and the IF statement.
Regards,
Mauritz
How about this
RangeSum( Sum(DowntimeDays), Sum({<WOId = {"=([Ended] - [Started]) >= 0 and Len(Trim(DowntimeDays)) = 0"}>} Ended - Started) )
Hi Sunny
Thanks for the reply. My understanding of rangesum is that it will return the sum of all the values. In your answer below I only want to get the second argument if the work order has no Downtime against it (if the first argument is 0).
Regards,
Mauritz
Have you tried it? Did you see the part highlighted in red?
Sum({<WOId = {"=([Ended] - [Started]) >= 0 and Len(Trim(DowntimeDays)) = 0"}>} Ended - Started)
Hi Sunny
Sorry, I missed it when I scrolled. I will have a look and let you know.
In your opinion, will this be a better approach to using the if statement in my original post?
Regards,
Mauritz
I believe so, but I would trust on your testing more than my word 🙂
Hi Sunny
I got it working with a slight variation:
RangeSum( Sum(DowntimeDays), Sum({<WOId = {"=([Ended] - [Started]) >= 0 and Sum(DowntimeDays) = 0"}>} Ended - Started) )
I guess the Len(Trim()) function did not work because I could have more than one downtime entry for a work order. I did not realise that you could use multiple conditions within a set expression between quotes (although it seems obvious now that you pointed it out).
I will let you know if I can find any change in the performance between your formula and the IF statement.
Regards,
Mauritz
Good to know that you managed to figure it out.
Best,
Sunny