Count per unique record

Fri, 19 Jul 2013 23:14:06 GMT

Another question: I have two tables with a one to many relationship. I want a count from the table where there is only one record and a sum on the table with many records. How do I do that?

Re: Count per unique record

Sokkorn — Sat, 20 Jul 2013 01:43:51 GMT

Hi,

Suppose that you have two table below

1. Invoice header:

Inv	Customer
1	A
2	B
3	C

2. Invoice details

Inv	Item	Amount
1	I001	10
1	I002	20
1	I003	30
2	I001	40
2	I002	50

So you link both table via [Invoice header],[Inv]=[Invoice details].[Inv]

From this we will got one to many relationship.

Now try to create Straight table with

1. Dimension: Inv and Customer

2. Expression

a. Count(Inv)

b. Sum(Amount)

It should give you

Inv	Customer	Count(Inv)	Sum(Amount)
1	A	1	60
2	B	1	90
3	C	1	0

Regards,

Sokkorn

topic Re: Count per unique record in QlikView

Count per unique record

Re: Count per unique record