Forum Discussion

jakeudy's avatar
jakeudy
Helper I
6 years ago
Solved

Group lines in table based on values in certain fields

I have a dataset that looks like this: ID $ Amount Delivery City Delivery State Name Created Date Sales Rep Created By Customer Account 57982 $100,000 Chicago IL Bob 3/1/2020 22 ...
Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    I have only given this a brief test but see how you get on

    ColAdd = VAR _city = TableQ[Delivery City]
         VAR _state = TableQ[Delivery State]
         VAR _amount = TableQ[$ Amount]
         VAR _cDate = TableQ[Created Date]
RETURN
   CALCULATE(MIN(TableQ[ID]), 
             FILTER(TableQ, TableQ[Delivery City] = _city &&
                            TableQ[Delivery State] = _state && 
                            ABS(TableQ[$ Amount] - _amount) < 10001 &&
                            ABS(DATEDIFF(TableQ[Created Date], _cDate, DAY)) < 14) )
HotChilli's avatar
HotChilli
Community Champion
6 years ago

I have only given this a brief test but see how you get on

ColAdd = VAR _city = TableQ[Delivery City]
         VAR _state = TableQ[Delivery State]
         VAR _amount = TableQ[$ Amount]
         VAR _cDate = TableQ[Created Date]
RETURN
   CALCULATE(MIN(TableQ[ID]), 
             FILTER(TableQ, TableQ[Delivery City] = _city &&
                            TableQ[Delivery State] = _state && 
                            ABS(TableQ[$ Amount] - _amount) < 10001 &&
                            ABS(DATEDIFF(TableQ[Created Date], _cDate, DAY)) < 14) )