topic Why is this preceding load not working in QlikView

Why is this preceding load not working

Wed, 25 Nov 2020 16:16:04 GMT

gets me the following error

Invalid expression

Data:

Load * Inline [

User, Doc

Holden,AR

Holden,SA

Holden,IA

Holden,ED

Holden,CS

Aaron,CS

Brent,AR

Brent,SA

Brent,IA

Brent,ED

]

Any help appreciated, thanks

Re: Why is this preceding load not working

swuehl — Fri, 04 Aug 2017 16:36:57 GMT

If you want to use aggregation functions like Sum() in your LOAD, you need to use a GROUP BY clause for all fields that are not embedded in aggregation functions.

Re: Why is this preceding load not working

sasiparupudi1 — Fri, 04 Aug 2017 16:38:20 GMT

You can not have aggregations in the preceeding loads.

change it to a resident load using the necessary group by statements.

finaltype is the problem as it is a result of aggregation

hth

Sasi

Re: Why is this preceding load not working

Fri, 04 Aug 2017 16:40:33 GMT

I'm not sure I understand.

The following script is still returning errors.

Re: Why is this preceding load not working

Fri, 04 Aug 2017 16:45:27 GMT

Okay, I did this resident load, and it works except the last sum = 3, for Admin doesn't show up

Re: Why is this preceding load not working

swuehl — Fri, 04 Aug 2017 16:46:30 GMT

Your top most star symbol will consider Type field as field which is not included in the GROUP by clause.

Re: Why is this preceding load not working

Clever_Anjos — Fri, 04 Aug 2017 16:47:03 GMT

Dont use * into your first load but

load

User,

Doc,

sum.....

Re: Why is this preceding load not working

swuehl — Fri, 04 Aug 2017 16:47:52 GMT

Your second load only creates 2 distinct values at most.

Re: Why is this preceding load not working

Fri, 04 Aug 2017 16:48:37 GMT

Ahk, youre right. Same issue though, the admin where type sum = 3 isnt showing up

Re: Why is this preceding load not working

sasiparupudi1 — Fri, 04 Aug 2017 16:48:53 GMT

I do not see a condition for type value 3 in your logic?

Re: Why is this preceding load not working

Fri, 04 Aug 2017 16:49:15 GMT

Oh, why is that?

How can I change it?

Re: Why is this preceding load not working

Fri, 04 Aug 2017 16:50:09 GMT

If it is just CS, the sum will be 1, if it is a doc not CS, the sum should be 2, and if it is both the sum should be 3.

Am I doing something wrong?

Re: Why is this preceding load not working

Fri, 04 Aug 2017 16:58:56 GMT

Could you provide me with a solution that would work? For finalType to have 3 sections based on CS, no-CS, and both

Re: Why is this preceding load not working

swuehl — Fri, 04 Aug 2017 17:08:10 GMT

I think you need to aggregate only per user (i.e. GROUP BY User), not by all three fields, which is too granular.

Re: Why is this preceding load not working

Fri, 04 Aug 2017 18:07:11 GMT

But if I load in user and group by user, then I lose the docs in my final load.

So now I'm left with just User and FinalType, no list of docs.

Any ideas?

Re: Why is this preceding load not working

sasiparupudi1 — Fri, 04 Aug 2017 18:31:32 GMT

If(DOC='CS',1,

If(DOC='AR',2,

If(DOC='IA',2,

If(DOC='ED',2,

If(DOC='SA',2

))))) As Type

The above condition is only giving values 1 and 2 as Type. Where is the classification that returns 3?

Re: Why is this preceding load not working

swuehl — Fri, 04 Aug 2017 18:44:27 GMT

Create another load for your detailed table.

Either join the table with user and FinalType to your original table or keep the tables linked by user.

Similar to what you have done in one of your previous answers.

Re: Why is this preceding load not working

Fri, 04 Aug 2017 18:46:50 GMT

I see, yeah that's what I did, just loaded it in again and it joins on User. Seems weird that there isn't an easier solution than this though

Thanks for your all your help though!

Re: Why is this preceding load not working

Fri, 04 Aug 2017 18:48:18 GMT

The 3 is gotten by the sum. So if user A has CS and ED, then it will be linked to both 1 and 2 under Type.

Then when I sum on distinct type, it should add up 1 and 2, and get 3, thus creating a third option.

There are 3 types of users in my data scenario, ones that can view CS docs, ones that can't view CS docs, and ones that can view both