Dynamic Straight/Pivot Table

[#1]

Dear all,

I need help on the following issue.

Supposed I have the following data

folder    viewer_id admin_id owner_id

1    B0001    B0001    B0001

2    B0001    B0002    B0003

3    B0001    B0002    B0003

4    B0001    B0002    B0003

5    B0001    B0002    B0004

6    B0003    B0005    B0004

7    B0003    B0005    B0004

8    B0004    B0005    B0005

9    B0006     B0005    B0006

10    B0006    B0005    B0001

11    B0006    B0003    B0001

12    B0006    B0003    B0001

13    B0001    B0003    B0001

I have put viewer id as dimension and count(viewer id) as the the first expression. The result is as what i expect. However, for admin count and owner count, I still can't get the expected result even I have tried numerous script and expression. I wonder if it is feasible in qlikview. Thanks a lot

Expected result
id	viewer count	admin count	owner count
B0001	6	1	5
B0006	4	0	1
B0003	2	3	3

[jagan]

Hi,

Try like this

Sample Script:

Data:

CrossTable(UserType, UserID)

LOAD

folder,

viewer_id AS Viewer,

admin_id AS Admin,

owner_id AS Owner

INLINE [

folder,viewer_id,admin_id, owner_id

1,B0001,B0001,B0001

2,B0001,B0002,B0003

3,B0001,B0002,B0003

4,B0001,B0002,B0003

5,B0001,B0002,B0004

6,B0003,B0005,B0004

7,B0003,B0005,B0004

8,B0004,B0005,B0005

9,B0006, B0005,B0006

10,B0006,B0005,B0001

11,B0006,B0003,B0001

12,B0006,B0003,B0001

13,B0001,B0003,B0001];

Create Straight table with the following

Dimension: UserID

Expressions:

Admin Count: Count({<UserType={'Admin'}>} UserID)

Viewer Count: Count({<UserType={'Viewer'}>} UserID)

Owner Count: Count({<UserType={'Owner'}>} UserID)

Hope this helps you.

Regards,

Jagan.

[ecolomer · Accepted solution]

Here you are my proposal

[#4]

Thanks a lot for your help. Both of the solution help!

[jagan]

Please close the thread.

Regards,

Jagan.