Calculate transactions per day

[#1]

Hi All,

I have a below table

Transaction ID	Transaction Date
1	1/1/2001
1	1/2/2001
2	1/2/2001
1	1/2/2001
3	1/3/2001

I am trying to identify transactions where the same ID was used more than once in a day. In the above example, I expect to have the output as     

Transaction ID	Transaction Date	count
1	1/2/2001	2

Can you please let me know the expressions I need to use to achieve the same?

[MK_QSL]

Create a Straight Table

Dimension

[Transaction Date]

Expression

COUNT(Distinct [Transaction ID])

[sunny_talwar · Accepted solution]

In the script you can do something like this:

yourTable:

LOAD [Transaction ID],

          [Transaction Date],

          [Transaction ID] & [Transaction Date] as Key

FROM yourSource

Join (yourTable)

LOAD Key,

          Count(DISTINCT Key) as Count

Resident yourTable

Group By Key;

HTH

Best,

S

[#4]

Another approach would be:

yourTable:

LOAD [Transaction ID],

          [Transaction Date],

          count(1) as TransactionCount

FROM yourSource

Group by [Transaction ID], [Transaction Date]