Load

[#1]

Hello,

In the script i have next:

StartSite:

SELECT * FROM TARGETTABLE;

---------------

In the script i want a new select statement that selects all results from Startsite with e.g. group by

GroupStartSite:

SELECT ColumnA, count(*) FROM <<StartSite>> GROUP BY ColumnA

How do i define a new select statement based on source earlier select statement in script?

Thx in advance

[Digvijay_Singh]

You need to use resident StartSite instead of FROM, it will do the needful, also we use load instead  of SELECT while loading from QV tables.

[swuehl]

Try

GroupStartSite:

LOAD ColumnA, count(ColumnA) as Count

RESIDENT StartSite

GROUP BY ColumnA;

[pokassov]

Hi!

load

     ColumnA,

     count(ColumnA)               As Cnt

Resident StartSite

group by ColumnA;

[sasiparupudi1]

StartSite:

SELECT * FROM TARGETTABLE;

//load the count using a resident load and do not concatenate the result to the main table(if it is your requirement)

final:

noconcatenate load

     Column1,

     count(Column1)               As Column1Count

Resident StartSite

group by Column1;

//finally drop the main table(if it is your requirement)

drop table StartSite;

hth

Sasi

[Gabriel]

Hi,

You don't really have to extract from database again. Since you've loaded the data into QlikView you have all the fields (*) in StartSite table and what you can do is extract fields that you want from StartSite table.

Use RESIDENT function

As suggested be swuehl

GroupStartSite:

LOAD ColumnA, count(ColumnA) as Count

RESIDENT StartSite

GROUP BY ColumnA;

[johnca]

Andreas, I have issue with your initial statement. When performing an aggregation in script I think it is bad practice to do a "Select *" (select everything). How would you know what fields to group by? Every field not aggregated is required to be in the Group By statement so you need to know all the field names in the table/view.

V/r,

John

[#8]

Hi John,

while I agree you might not want to do a load * from the underlying DB.

"Every field not aggregated is required to be in the Group By statement"

This isn't quite right in the QV group by, you don't put in all the fields, just those you want to aggregate by.

hope that helps

Joe

[johnca]

Joe, thanks for agreeing with part of it anyway.

I stand corrected.

[#10]

no worries glad to help