topic Re: Dynamic Straight/Pivot Table in QlikView

Dynamic Straight/Pivot Table

Tue, 19 Aug 2014 01:58:06 GMT

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

Re: Dynamic Straight/Pivot Table

jagan — Tue, 19 Aug 2014 02:33:08 GMT

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.

Re: Dynamic Straight/Pivot Table

ecolomer — Tue, 19 Aug 2014 03:06:09 GMT

Here you are my proposal

Re: Dynamic Straight/Pivot Table

Wed, 20 Aug 2014 11:09:18 GMT

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

Re: Dynamic Straight/Pivot Table

jagan — Wed, 20 Aug 2014 14:15:10 GMT

Please close the thread.

Regards,

Jagan.