topic Re: Load in QlikView

Load

Tue, 29 Sep 2015 13:36:15 GMT

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

Re: Load

Digvijay_Singh — Tue, 29 Sep 2015 13:39:48 GMT

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.

Re: Load

swuehl — Tue, 29 Sep 2015 13:39:58 GMT

Try

GroupStartSite:

LOAD ColumnA, count(ColumnA) as Count

RESIDENT StartSite

GROUP BY ColumnA;

Re: Load

pokassov — Tue, 29 Sep 2015 13:40:31 GMT

Hi!

load

ColumnA,

count(ColumnA) As Cnt

Resident StartSite

group by ColumnA;

Re: Load

sasiparupudi1 — Tue, 29 Sep 2015 13:50:20 GMT

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

Re: Load

Gabriel — Tue, 29 Sep 2015 14:02:43 GMT

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;

Re: Load

johnca — Tue, 29 Sep 2015 15:25:32 GMT

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

Re: Load

Tue, 29 Sep 2015 15:36:09 GMT

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

Re: Load

johnca — Tue, 29 Sep 2015 15:40:24 GMT

Joe, thanks for agreeing with part of it anyway.

I stand corrected.

Re: Load

Tue, 29 Sep 2015 15:42:34 GMT

no worries glad to help

Re: Load

johnca — Mon, 05 Oct 2015 16:59:59 GMT

Actually, it's confused me more. I can't seem to find an example that doesn't group all unaggregated fields in a query.

Re: Load

Tue, 06 Oct 2015 08:24:31 GMT

Hi John,

please see attached, the base data has 4 dimensions and you can see different grouped up tables created from this. Each table needs the fields to aggregate by included in the group by

hope that helps

Joe

Re: Load

tamilarasu — Tue, 06 Oct 2015 09:33:47 GMT

Hi JoeSimmons,

Could you explain what is happening behind the script. I just want to understand Group by statement here.

Re: Load

Tue, 06 Oct 2015 09:52:08 GMT

Hi Tamil,

this was me trying to show an example of entering all the underlying dimensions in the group by, still with a limited load of dimensions.

So the code will group by all 4 fields and create you a row for each aggregated entry (based on the 4). as you only have a single field in the load statement, you will see on the output multiple entries for each value in FieldA.

If however you only have the FieldA in the group by, you'll get a single row for each value.

I was just trying to show the difference in output between those two really.

Hope that helps

Joe

Re: Load

johnca — Tue, 06 Oct 2015 15:34:41 GMT

Hi Joe,

I was going to say your examples were exactly what I was saying until I looked closely at the last one. I get the first ones, but I'm still stumped why someone may want to do the last one. Thanks for confusing me more. 🙂

V/r,

John

Re: Load

Tue, 06 Oct 2015 15:50:48 GMT

Hi John,

I'm not saying you would want to the last one, if you only have the one field loading you have extra rows for not much reason.

I was just trying to show what would happen if you include all the fields in the group by in that example, just to illustrate you may not want all fields in your group by

Hope that helps

Joe