Finding the Zero groups frequency

[gurkan]

Hi All,

I have a table with 48 columns. How can I find the frequency of the Zero groups? For example, there are 4 zero groups below. I solved it using nested loops but it is so slow for 300K rows. 

value1	value2	value3	value4	value5	value6	value7	value8	value9	value10	value11	value12	value13	value14	value15	value16	value17	value18	value19	value20	value21	value22	value23	value24	value25	value26	value27	value28	value29	value30	value31	value32	value33	value34	value35	value36	value37	value38	value39	value40	value41	value42	value43	value44	value45	value46	value47	value48
1	1	1	1	0	0	0	1	1	2	1	0	0	0	0	0	0	0	0	2	1	1	1	3	0	0	0	0	0	0	0	1	4	5	5	5	5	5	0	0	0	0	0	0	0	0	0	0

[gurkan]

I can do it using this formula in Excel. =SUM(IF(FREQUENCY(IF(D1:BD1=0, COLUMN(D1:BD1)), IF(D1:BD1<>0, COLUMN(D1:BD1)))>0, 1)) . But I need a data-loading script.

[rwunderlich]

You can do this either in script or chart expression using the RangeSum() function.

Data:
LOAD 
  *,
  -RangeSum(Value1=0, Value2=0, Value3=0, Value4=0) as ZeroCount
Inline [
ID, Value1, Value2, Value3, Value4
100, 1, 1, 0, 0
101, 0, 1, 1, 1
102, 1, 1, 1, 1
103, 0, 0, 0, 0
];

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[gurkan]

Hi Rwunderlich,

I don't want to count zeros. I want to count zero groups. Zero values represent energy outages per half hour (total 48 values per day) and I want to count outages per day regardless of the total duration. As I gave the example below. for value5, value6, value7 is 1 outage group. 

value1

value2

value3

value4

value5

value6

value7

value8

value9

value10

value11

value12

value13

value14

value15

value16

value17

value18

value19

value20

value21

value22

value23

value24

value25

value26

value27

value28

value29

value30

value31

value32

value33

value34

value35

value36

value37

value38

value39

value40

value41

value42

value43

value44

value45

value46

value47

value48

1

1

1

1

0

0

0

1

[rwunderlich · Accepted solution]

I see. How about:

Data:
LOAD *
Inline [
ID, Value1, Value2, Value3, Value4
100, 1, 1, 0, 0
101, 0, 1, 1, 1
102, 1, 1, 1, 1
103, 0, 0, 0, 0
104, 0, 1, 0, 0
];

Counts:
LOAD 
 ID,
 Count(ZeroGroup) as ZeroGroupCount
Where len(ZeroGroup) > 0
Group By ID
;
LOAD 
 ID,
 Text(SubField(Value1&Value2&Value3&Value4, '1')) as ZeroGroup
Resident Data;

-Rob

[gurkan]

Hi Rwunderlich,

Thank you so much it works! You are great!