Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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))
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
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
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))