topic Re: How to create an efficient view. in QlikView

How to create an efficient view.

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to create an efficient view.

adamdavi3s — Mon, 13 Mar 2017 10:43:36 GMT

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

Re: How to create an efficient view.

Mon, 13 Mar 2017 10:51:17 GMT

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

];

Re: How to create an efficient view.

ahaahaaha — Wed, 15 Mar 2017 07:12:34 GMT

Hi Nobuko,

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

Regards,

Andrey

Re: How to create an efficient view.

Wed, 15 Mar 2017 07:22:11 GMT

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