How to create an efficient view.

[#1]

I have a data like below.

=================

UserData:

LOAD * INLINE [

    UserName, Category, Goal, Actual

    UserA, AAA, 4, 3

    UserA, BBB, 5, 6

    UserA, CCC, 4, 5

    UserA, DDD, 5, 6

    UserA, EEE, 3, 2

    UserA, FFF, 4, 3

    UserB, AAA, 4, 3

    UserB, BBB, 5, 4

    UserB, CCC, 4, 5

    UserB, DDD, 5, 6

    UserB, EEE, 3, 2

    UserB, FFF, 4, 6

];

UserAnalysis:

LOAD *,

If(Actual-Goal>0,1,0) as Improved

Resident UserData;

========================

User analysis data will be like below based on above

    UserA, AAA, 4, 3, 0

    UserA, BBB, 5, 6, 1

    UserA, CCC, 4, 5, 1

    UserA, DDD, 5, 6, 1

    UserA, EEE, 3, 2, 0

    UserA, FFF, 4, 3, 0

    UserB, AAA, 4, 3, 0

    UserB, BBB, 5, 4, 0

    UserB, CCC, 4, 5, 1

    UserB, DDD, 5, 6, 1

    UserB, EEE, 3, 2, 0

    UserB, FFF, 4, 6, 1

In spearate [Taget] table, it shows each Target required which categories

    Category   AAA  BBB  CCC  DDD  EEE

    TargetA     0       1       1        0        0

    TargetB     0       0       1        1        0

    TargetC     0       0       0        1        0

Question

I'd like shows each user cover which target based on target table. As a result, I'd like to get following result on qlikview. qlikview shows the reslut per user.

UserA have Target A,B,C.

UserB have Target B and C

I appreciate if you can share me a solution for this.

[adamdavi3s]

Can you please share the load inline for your target table as well?

This will be possible with a formula like

Concat(aggr(only()))

but without knowing how the target table is loaded / structured I can't advise fully

[#3]

Thank you for reply.  Here it is.

TargetTable:

LOAD * INLINE [

    Category, AAA, BBB, CCC, DDD, EEE

    TargetA, 0, 1, 1, 0, 0

    TargetB, 0, 0, 1, 1, 0

    TargetC, 0, 0, 0, 1, 0

];

[ahaahaaha]

Hi Nobuko,

The logic of the connection between UserName, Category(AAA, BBB etc.) and Category(TargetA, TargetB etc.) is not very clear.

Regards,

Andrey

[#5]

My bad.... It should be like below.

TargetTable:

LOAD * INLINE [

    Target, AAA, BBB, CCC, DDD, EEE

    TargetA, 0, 1, 1, 0, 0

    TargetB, 0, 0, 1, 1, 0

    TargetC, 0, 0, 0, 1, 0

];

This means, TargetA requires above the goal on category BBB and CCC. It does not matter whether other category is below or above.

Target B requires above goal on category CCC and DDD.

Target C requires above goal on cateogry DDD.

Therefore, based on target table, I am expecting to have following result.

UserA have Target A,B,C.

UserB have Target B and C