topic Re: Set analysis using one field if another field does not exist in App Development

Set analysis using one field if another field does not exist

Mauritz_SA — Mon, 15 Apr 2019 09:28:25 GMT

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

@sunny_talwar @Gysbert_Wassenaar @hic @swuehl

Re: Set analysis using one field if another field does not exist

sunny_talwar — Mon, 15 Apr 2019 11:33:19 GMT

How about this

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

Re: Set analysis using one field if another field does not exist

Mauritz_SA — Mon, 15 Apr 2019 14:06:31 GMT

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

Re: Set analysis using one field if another field does not exist

sunny_talwar — Mon, 15 Apr 2019 16:03:18 GMT

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

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

Re: Set analysis using one field if another field does not exist

Mauritz_SA — Mon, 15 Apr 2019 16:06:57 GMT

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

Re: Set analysis using one field if another field does not exist

sunny_talwar — Mon, 15 Apr 2019 16:16:58 GMT

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

Re: Set analysis using one field if another field does not exist

Mauritz_SA — Mon, 15 Apr 2019 19:55:06 GMT

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

Re: Set analysis using one field if another field does not exist

sunny_talwar — Mon, 15 Apr 2019 23:58:57 GMT

Good to know that you managed to figure it out.

Best,
Sunny