Equipment can be repeated, so for an equipment more than one row can exists in the table.
Based on the above table I am trying to count the number of rows that Date field is between a date selected by user (Selected_Date). I want to do this calculation for each equipment and finally I want to obtain the total sum of all equipment. Note that third row has Equipment field empty so I want to discard this row from Aggr function. so I indicate a set analysis within aggr function. I have below expression:
If for example user selects date range [02/01/2014, 03/01/2014], so Min(Selected_Date) is 02/01/2014 and Max(Selected_Date) is 03/01/2014, I am expecting to obtain 3 as a result from the above table but instead I am getting 4. It seems like Aggr is taken into account third row which has Equipment field empty. I want to discard this row. How can I do this?
Aggr function takes blank as a value, with {$<[Equipment]={'*'}>} you will ignore Nulls, but not blanks. Blank values aren't null. I think that is better to modify blank values by null values on script, on non description fields
Do you really need the aggr() in this case? I mean if you are only interested in counting the number of equipments can't you just do that using count distinct?
For each equipment I need to obtain the number of rows which date is in the date range selected. This is the reason why I need Aggr and not only Count.
But filtering Equipment in LOAD in the script, empty Equipments are not taken into account and not loaded. In my case I need to load empty Equipment because I need them for other calculations for other purposes.
