Unique Numbers In Holistics

Ask the Community
1

I have 3 columns :: Meeting ID - Expense Type ID - Number All these columns they have a data like below

Meeting ID - Expense Type ID - Number 
2000                   5         1
2000                   7         2
2001                   5         3
2001                   5         4
2002                   9         5
2002                   21        5
2002                   23        5
2003                   9         6
2004                   21        7
2005                   2         8
2005                   2         8

The above just an example of what I have and what the result that I need ::

  • If the meeting ID is same and the expense is not in (2,9,21,22,23) then put in the last column a unique number like 1 , 2 , 3 etc … for each row
  • If the meeting ID is same for and the expense is = 2 then put the same number in the last column like the above example (2005)
  • If the meeting ID is same and the expense is = 9 or 21 or 22 or 23 then put the same number in the last column as the above example … (2002)
  • If the meeting ID is not same then it must have a unique number as the example above … (2003 , 2004)

I couldn’t solve that using MySQL only … Is there a way in Holistics can help me? Or if anyone please can help me if we have a MySQL query for this?

1 Like
2

Hi @Hamza_rashed,

If I understand your case correctly, you want to create a Number column with the following logic:

  • Grouping for Special Expense Types (2, 9, 21, 22, 23): Rows with the same meeting_id and any of the following expense_type_id values: 2, 9, 21, 22, 23 should share the same number.

    • Examples:
      • For meeting_id = 2002, all rows with expense_type_id = 9, 21, or 23 will have the same number (5 in this case).
      • For meeting_id = 2005, both rows with expense_type_id = 2 will have the same number (8 in this case).

  • Grouping for Other Expense Types:

    • Rows with the same meeting_id and an expense_type_id not in the group (2, 9, 21, 22, 23) should receive sequential unique numbers.
      • Example: For meeting_id = 2000, the rows with expense_type_id = 5 and 7 will receive distinct numbers (1 and 2 respectively)
    • Rows with different meeting_id should receive sequential unique numbers.
      • Example: For meeting_id = 2002, and 2003 will receive distinct number (5 and 6 respectively)

Please let me know if I misunderstood anything.
If this matches your requirements, you can use our Transform Model (or Query Model) to implement this logic.

If you’d like us to provide detailed guidance on writing the query, please confirm the use case so we can assist further.

Side question: Why did you separate the case expense_type_id = 2 and expense_type_id is in (9, 21, 22, 23)? It looks like they have the same logic.

1 Like