Get the count of a value which has no subset, since its the association of two tables

[Applicable88]

Hello,

I have two tables. These are linked via the Order ID. 

On one side I have the planned orders to be made.  And on the other side I have a simple table with the finished Orders and when  it was finished:

Order Id	timestamp
1234	16.06.2020
1235	16.06.2020
1236	16.06.2020
1237	16.06.2020

In the visualization I build a table with all the order ID's and the one I finished:

count()	if(timestamp>0, finished, closed
Order	Status
1234	finished
1235	finished
1236	finished
1237	finished
1238	-
1239	-
1240	-
1241	-
1242	-
1243	-
1244	-
1245	-
1246	-

Since the ordernumberID is the linked association, all the numbers are null on the other side when the order has no timestamp yet. 

I need to count all orders which aren't finished yet. So it would mean to count these lines where the respective orderID has an '-' on the otherside. Since no timestamp.The problem is I can't cound null or empty fields. Since they have no subset yet I don't even know is it a Null, 0 or empty field? 

My second question : Since the subset is still not there also the fields cannot expressed with:

 if(timestamp>0, 'finished', 'closed')

Since again its empty no 'closed' expression can be put there. 

I hope someone has an solution to that. Note that a simple count(orderID) won’t work, since I don’t want count all. I only want to count the orderID  without a timestamp yet based on today for example. The date column wasn’t put in to simplify my explanation.

Thank you.

[lironbaram · Accepted solution]

hi 

for creating a dimension of status , use this expression 

=aggr(if(timeStamp>0,'finished','closed'),OrderId)

for counting the number of open orders you can use this expression 

count(distinct if(len(timeStamp)<2,OrderId))

another way will be to do it in the script , to flag open orders , and to calculate the status 

[Applicable88]

Hello @lironbaram ,

thank you very much for the quick reply.

The first one works out

=aggr(if(timeStamp>0,'finished','closed'),OrderId)

I have a question why  if(timestamp>0,'finished', 'closed' ) haven't return me the same value?

The other one to solve the 2nd problem 

count(distinct if(len(timeStamp)<2,OrderId))

returns too much value. I just want to return these values without timestamps, empty or null,(I don't know for sure) 

But

[lironbaram · Accepted solution]

hi 

for the dimension 

the reason simple if didn't work is that there is no value for the field timestamp for the orders , and your expression is calculated on the timestamp field  directly , while the aggr function creates a virtual table with two columns order and the calculation so now you have a null value for timestamp for each order and you can calculate the expression 

as for the counter 

it worked on demo data but you can use similar approach like this 

sum(aggr(if(timeStamp>0,0,1),OrderId))