topic Populated calculated field in script in QlikView

Populated calculated field in script

Tue, 22 Jan 2013 14:57:13 GMT

Hi Guys,

Just started using Qlikview and I think maybe I am being slow.

I have a table:

Title: Tbl_Request

Fields: RequestID

I want to be able to load another field which counts the number of each RequestIDs which are found for each record.

eg:

RequestID: Count:

10 3

25 1

20 1

19 1

20 2

Is there a way to calculate this field using a LOAD statement?

Tbl_Request:

LOAD RequestID,

<<<Expression for Count Field>>> As Count;

SQL SELECT *

FROM RequestAction;

Any help would be appreciated.

Many Thanks

Re: Populated calculated field in script

swuehl — Tue, 22 Jan 2013 14:59:26 GMT

Use a subsequent LOAD with a group by clause and a count() aggregation function:

...

LOAD RequestID,

count(RequestID) as Count

resident Tbl_Request group by RequestID;

Re: Populated calculated field in script

MayilVahanan — Tue, 22 Jan 2013 14:59:51 GMT

Try like this

Load
RequestID,

Count(RequestID) As Count

From tablename // resident tablename

Group by RequestID;

Re: Populated calculated field in script

jagan — Tue, 22 Jan 2013 15:12:04 GMT

Hi,

Try this script

Requests:

LOAD

RequestID

FROM Tbl_Request;

INNER JOIN (Requests)

LOAD

RequestID,

Count(RequestID) AS Count

Resident Requests;

Hope this helps you.

Regards,

Jagan.

Re: Populated calculated field in script

Tue, 22 Jan 2013 16:07:12 GMT

Hi Guys,

I am having trouble getting this to work.

I think it is becasue some of the names have changed in my data.

I am running the following script:

Requests:
LOAD
ID as RequestActionID;
SQL SELECT *

FROM RequestAction;

Inner JOIN (Requests)
LOAD

RequestActionID,

Count(RequestActionID) AS CountOfID
RESIDENT Requests;

but i get this error message when I try to reload:

Invalid expression

Inner JOIN (Requests)

LOAD

RequestActionID,

Count(RequestActionID) AS CountOfID

RESIDENT Requests

Any Ideas? I bet it's somehting really obvious!

Re: Populated calculated field in script

Anonymous — Tue, 22 Jan 2013 17:01:21 GMT

I guess you forogt the group by clause.

Requests:
LOAD
ID as RequestActionID;
SQL SELECT *

FROM RequestAction;

Inner JOIN (Requests)
LOAD

RequestActionID,

Count(RequestActionID) AS CountOfID
RESIDENT Requests

GROUP BY RequestActionID;

Re: Populated calculated field in script

jagan — Wed, 23 Jan 2013 09:57:10 GMT

Hi,

Missed Group by clause

Requests:
LOAD
ID as RequestActionID;
SQL SELECT *
FROM RequestAction;

Inner JOIN (Requests)
LOAD
RequestActionID,
Count(RequestActionID) AS CountOfID
RESIDENT Requests
GROUP BY RequestActionID;

Regards,

Jagan.

Re: Populated calculated field in script

Wed, 23 Jan 2013 10:01:13 GMT

Many Many Thanks Jagan!

Works perfectly!

Re: Populated calculated field in script

Anonymous — Wed, 23 Jan 2013 10:48:32 GMT

I wonder if there is a time zone problem. Because I had replied earlier to this post with the correct answer

Regards

MultiView

Re: Populated calculated field in script

Wed, 23 Jan 2013 10:52:27 GMT

hmmm,

I didn't see that post Multiview

you are quite right, it is the same script!

Have given you credit as you got there first!

Thanks for the help!