Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate transactions per day

Hi All,

I have a below table

Transaction IDTransaction Date
11/1/2001
11/2/2001
21/2/2001
11/2/2001
31/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 IDTransaction Datecount
11/2/20012

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
MK_QSL
MVP
MVP

Create a Straight Table

Dimension

[Transaction Date]

Expression

COUNT(Distinct [Transaction ID])

sunny_talwar

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

View solution in original post

Not applicable
Author

Another approach would be:

yourTable:

LOAD [Transaction ID],

          [Transaction Date],

          count(1) as TransactionCount

FROM yourSource

Group by [Transaction ID], [Transaction Date]