0

exclude equal date

Hi,
I need to count the number of records in a table excluding those with equal dates... please could someone help me?

I write this but don't work:

let d := Table1.Date;
if Table1.Datea = d then
cnt(Table1.Date)
end

12 replies

null
    • Sal
    • 5 yrs ago
    • Reported - view

    sorry...

    let d := Table1.Date;
    if Table1.Date = d then
    cnt(Table1.Date)
    end

    • Nick
    • 5 yrs ago
    • Reported - view

    Try it this way:

     

    let d := Date;
    cnt((select Table1 where Date = d).Id)

    or

    let d := Date;
    cnt((select Table1 where Date != d).Id)

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    cnt(select Table1 where Date != d)

    Steven.

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    Uh Oh, Nick again....😵

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    Sorry...

    • Sal
    • 5 yrs ago
    • Reported - view

    Thank you but...

    I've tried in every way:
    let d := Table1.Date;
    cnt(select Table1 where Date != d)

    and after:

    let d := Table1.Date;
    cnt((select Table1 where Date != d).Id)

    but the result is the same... counts all records and doesn't exclude those with the same date...

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    Ah, misunderstood your question first, try this in a formula field:

    let c := 0;
    let dates := unique((select Table1). Date;
    for i from 0 to cnt(dates) do
    if cnt(select Table1 where Date = item(dates, i)) = 1 then
    c := c + 1
    end
    end;
    c

    Steven.

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view
    • Sal
    • 5 yrs ago
    • Reported - view

    Thank you it work!

    Anyway, in this way it excludes all duplicates ... how can I keep only one of the duplicate records?

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    To count only  unique values in that list is much easier , use this:
    cnt(unique((select Table1).Date)

    • Sal
    • 5 yrs ago
    • Reported - view

    Steven... I'm sorry but where can I put this code?

    I've tried in: if cnt(unique(select Table1.Date) then... bud don't work!

    let n := 0;
    let dates := unique(select Table1).Date;
    for i from 0 to cnt(dates) do
    if cnt(select Table1 where Date = item(dates, i)) = 1 then
    n := n + 1
    end
    end;
    n

    Thank you very much!

    • Sal
    • 5 yrs ago
    • Reported - view

    ok solved!

    I put only code: 

    cnt(unique((select Table1).Date)

    thank you!

Login to reply

Content aside

  • 5 yrs agoLast active
  • 12Replies
  • 1877Views