Populated calculated field in script

[#1]

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

10                        3

10                        3

25                        1

20                        1

19                        1

20                        2

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

L

[swuehl]

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;

[MayilVahanan]

Hi

Try like this

Load
RequestID,

Count(RequestID) As Count

From tablename                               // resident tablename

Group by RequestID;

[jagan]

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.

[#5]

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!

L

[Anonymous · Accepted solution]

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;

[jagan]

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.

[#8]

Many Many Thanks Jagan!

Works perfectly!

[Anonymous]

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

Regards

MultiView

[#10]

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!

L