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: 
Mauritz_SA
Partner - Specialist
Partner - Specialist

Set analysis using one field if another field does not exist

Hi there experts

I have two tables, one with a work order and one with downtimes like below:

WO:

WOIdStartedEnded
12019/01/012019/01/15
22019/01/052019/01/10
32019/01/092019/01/08
42019/01/07 

 

Downtime:

WOIdDowntimeTypeDowntimeDays
1A5
1B1
3A0.5
3C1

 

When the two are concatenated have the following table:

WOIdStartedEndedDowntimeTypeDowntimeDays
12019/01/012019/01/15A5
12019/01/012019/01/15B1
22019/01/052019/01/10  
32019/01/092019/01/08A0.5
32019/01/092019/01/08C1
42019/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:

WOIdDowntime
16
25
31.5
40

 

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 Smiley Wink.

Thank you in advance for any help!

Mauritz

@sunny_talwar @Gysbert_Wassenaar @hic @swuehl 

Labels (1)
1 Solution

Accepted Solutions
Mauritz_SA
Partner - Specialist
Partner - Specialist
Author

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

View solution in original post

7 Replies
sunny_talwar

How about this

RangeSum(
  Sum(DowntimeDays),
  Sum({<WOId = {"=([Ended] - [Started]) >= 0 and Len(Trim(DowntimeDays)) = 0"}>} Ended - Started)
)
Mauritz_SA
Partner - Specialist
Partner - Specialist
Author

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

sunny_talwar

Have you tried it? Did you see the part highlighted in red?

Sum({<WOId = {"=([Ended] - [Started]) >= 0 and Len(Trim(DowntimeDays)) = 0"}>} Ended - Started)
Mauritz_SA
Partner - Specialist
Partner - Specialist
Author

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

sunny_talwar

I believe so, but I would trust on your testing more than my word 🙂

Mauritz_SA
Partner - Specialist
Partner - Specialist
Author

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

sunny_talwar

Good to know that you managed to figure it out.

Best,
Sunny