Multiply values in column

[iglou]

Not sure if this was asked before. 

I would like to multiply the "Availability" column for the same system based on the table below:

LOAD * INLINE [

System, Sub-system, Availability

A, A1, 1

A, A2, 0

A, A3, 1

B, B1, 1

B, B2, 1

C, C1, 0

C, C2, 0

]

The final table should look like this:

System, Availability

A, 0

B, 1

C, 0

Any idea what could be a good way to do this?

[justISO]

Hi, if 'Availability' field only have 0/1 values, maybe something like this:

DATA_temp:
LOAD * INLINE [
System, Sub-system, Availability
A, A1, 1
A, A2, 0
A, A3, 1
B, B1, 1
B, B2, 1
C, C1, 0
C, C2, 0
];

MAIN:
LOAD
System,
if(sum(Availability)<>count(System), 0, 1) as Availability
RESIDENT DATA_temp
GROUP BY System;

DROP TABLE DATA_temp;

[iglou]

Sorry I wasn't clear in my question.

It is true that availability contains 1 or 0 only.

So basically, if there is any 0 in the sub-system, my entire system is unavailable (ie, should return 0)

The output I wanted to show is in the chart view, not in the script view.

[marcus_sommer]

Maybe you could apply the following logic:

t1: LOAD * INLINE [
System, Sub-system, Availability
A, A1, 1
A, A2, 0
A, A3, 1
B, B1, 1
B, B2, 1
C, C1, 0
C, C2, 0];

m: mapping load distinct System, [Sub-System]
resident t1 where [Sub-System] = 0;

t2: load distinct System, applymap('m', System, 1) as Availability
resident t1;

- Marcus

[justISO · Accepted solution]

So the way to get this wanted result at report level with my suggestion is very similar, just use

if(sum(Availability)<>count(System), 0, 1)

[iglou]

Got it! Thanks! 😄

[MarcoWedel]

Min(Availability)