Using Distinct within a complex Set expression

[Lena4]

Is it possible to use distinct within a complex set expression?  Basically, I want to look the P locations and  if any of the combos have 'open' values,  concat the timestamp so I can use it in my set expression.

The goal is to get the count of timestamps at Location A, but I only want to count the timestamps where the P locations have values of 'Open'.

Here is a sample, there are several more lines. It does produce an output, but I think it's duplicating timestamps because the 'value ={">100"}' part of the expression is not working, I get the same result if I remove it.  I think that I need to figure out how to get a distinct list of all of the tags as a whole, and not just the combos.  Any ideas about how I can solve this problem?

I'd prefer chart solutions as the script solutions I've tried aren't efficient as the data needed is in two different tables and one has millions of lines.

[henrikalmen]

Please provide some examples. A small example of raw data and what result you expect. I'm not sure what it is that you are trying to achieve, but I'm kind of thinking that you're not on the right path with the given expression example.

[Lena4]

Hi @henrikalmen - thanks for taking a look. 

Here is a simplified example. This data is all in one table, but I broke it apart for ease. Using the P.2/P.12 combo as an example, looking at the data to the right, I want to check to see when P.2/P.12 are Open at the same time and then look for those exact timestamps for Location A on the left. I want to count the timestamps where the Value at Location A is > 100. I would do that for each combo (P.1/P.11, P.2/P.12, P.3/P.13) and at the end. So, I would expect that for location P.2/P.12, it should return 10, but it is returning 13 and I'm not sure why. 

[henrikalmen]

Do you mean that from the data in the image you posted now, the result should be 10? How? What is it that you count? There are two marked rows to the left where value is 100 or more, and six rows to the right. What gives you 10? Shouldn't the result in this example be 2?

Also, what do you want the result to look like? Is it a table listing, something like this?

Location   NoOfTimestamps
P1/P11     2
P2/P12     2
P3/P13     1

[Lena4]

My mistake, you are correct. The p1/p11 result is 2 and  the result overall should be 5 but it's including the timestamps that are <100, so I'm getting 8.

[henrikalmen · Accepted solution]

I kind of think that it should be done in script, if possible. This is an example solution that gives you a new table where you can sum the field pcombocount to get your expected result. But this is possibly not at all what you want, depending on your actual needs with your actual data. But maybe it will point you in a direction if solving this in script is an acceptable way to go.

data:
load * inline [
TIMESTAMP Location Value
6/24/2004 0:00:00 A 500
6/24/2004 1:00:00 A 5
6/24/2004 2:00:00 A 200
6/24/2004 3:00:00 A 300
6/24/2004 7:00:00 A 50
6/24/2004 8:00:00 A 400
6/24/2004 9:00:00 A 200
6/24/2004 10:00:00 A 200
6/24/2004 11:00:00 A 10
6/24/2004 12:00:00 A 500
6/24/2004 13:00:00 A 500
6/24/2004 14:00:00 A 300
6/24/2004 15:00:00 A 0
6/24/2004 0:00:00 P.1 Open
6/24/2004 0:00:00 P.11 Open
6/24/2004 1:00:00 P.1 Open
6/24/2004 1:00:00 P.11 Open
6/24/2004 2:00:00 P.1 Open
6/24/2004 2:00:00 P.11 Open
6/24/2004 3:00:00 P.2 Open
6/24/2004 3:00:00 P.12 Open
6/24/2004 7:00:00 P.2 Open
6/24/2004 7:00:00 P.12 Open
6/24/2004 8:00:00 P.2 Open
6/24/2004 8:00:00 P.12 Open
6/24/2004 10:00:00 P.3 Open
6/24/2004 10:00:00 P.13 Open
6/24/2004 11:00:00 P.3 Open
6/24/2004 11:00:00 P.13 Open
] (delimiter is '\t');

pcombo:
load 
    ptime, 
    pbase,
    concat(p,'/') as pcombo
group by ptime, pbase
;
load
   TIMESTAMP as ptime, 
   Location as p, 
   right(subfield(Location,'.',2),1) as pbase
resident data
where Location like 'P*'
and Value='Open'
;

drop field pbase;

left join(pcombo)
load TIMESTAMP as ptime, sum(1) as pcombocount group by TIMESTAMP;
load TIMESTAMP, Value 
resident data
where Location = 'A'
and Value>=100
;

pcombo2: 
noconcatenate load ptime as TIMESTAMP, pcombo, pcombocount resident pcombo where pcombocount>0  and pcombo like '*/*';;

drop table pcombo;

This is a really quick hack, I need to go now...

[Lena4]

@henrikalmen Thank you, I'll work on a script solution.  You example is very helpful.