topic Re: Calculate transactions per day in QlikView

Calculate transactions per day

Mon, 23 Feb 2015 17:27:15 GMT

Hi All,

I have a below table

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 — Mon, 23 Feb 2015 17:36:56 GMT

Create a Straight Table

Dimension

[Transaction Date]

Expression

COUNT(Distinct [Transaction ID])

sunny_talwar — Mon, 23 Feb 2015 17:42:29 GMT

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,

Mon, 23 Feb 2015 22:29:48 GMT

Another approach would be:

yourTable:

LOAD [Transaction ID],

[Transaction Date],

count(1) as TransactionCount

FROM yourSource

Group by [Transaction ID], [Transaction Date]