Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Create a Straight Table
Dimension
[Transaction Date]
Expression
COUNT(Distinct [Transaction ID])
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
Another approach would be:
yourTable:
LOAD [Transaction ID],
[Transaction Date],
count(1) as TransactionCount
FROM yourSource
Group by [Transaction ID], [Transaction Date]