topic Re: Using Group in the load script like aggr in QlikView

Using Group in the load script like aggr

Fri, 27 Feb 2015 14:54:35 GMT

Hi there,

i have a database of objects with certain reoccuring ID's which have fields attached to them.

I want to load all the entries where Field1 = 'X' and the total number of field2 entries with the value ='D' is bigger than 5.

I manged to get a list of IDs which match that criteria but i have troubles getting them into my Load Script since i cant use the aggr function there.

In the LoadScript i used a if to flag all my matches

Load

* ,
if (Field2= 'D',1,0) as DTYPE,

from .......

And then i reload it using:

F15_select:

Load *
Resident F15_merged
Where Field1 = 'X'

and (here would probably come something like the expression below);

In Qlik view i created a ListBox with the expression

if(aggr(sum(RTYPE), ID)>5,ID)

Which creates a list of IDs that match my criteria.

How can i translate that into the Load Script so only the List of IDs above is loaded?

I figured i have to use group by, but i havent figured out how.

Can somebody help me?

Thanks

Julian

Re: Using Group in the load script like aggr

chrismarlow — Fri, 27 Feb 2015 15:02:44 GMT

Is that Field1='X' and Sum(RTYPE) by ID>5 or Field1='X' or Sum(RTYPE) by ID>5.

I would try to build this up by identifying rows so I you can debug, before tidying up.

So for the Sum(RTYPE) I would create a table;

sum_rtype:

Load

ID,

Sum(RTYPE)

Resident xxx

Group By ID;

Then take a look at that, see if it makes sense, if it does left join back to the original table (either directly or by a new load then dropping) and then it depends on your answer to my question above ... I think.

Re: Using Group in the load script like aggr

anbu1984 — Mon, 02 Mar 2015 07:17:25 GMT

sum_rtype:

Load * Where SumRTYPE > 5;

Load

ID,

Sum(RTYPE) As SumRTYPE

Resident xxx

Group By ID;

Re: Using Group in the load script like aggr

Tue, 03 Mar 2015 10:49:33 GMT

Its an AND condition that is needed.

I tried loading the new table as you suggested, but i don't really understand how i would be able to join the result into my existing table.

anbu: Why would i use the SumRTYPE before i create it below? How can i use the load statement without a from or resident?

Edit:

so I tried this:

table1:

Load * ,

if (DATEITYP = 'R',1,0) as RTYPE

From xxx.qvd (qvd);

sum_rtype:

Load

*,
Sum(RTYPE) as SumRTYPE

Resident table1

Group By ID;

DROP table table1;

table1_select:

Load *

Resident sum_rtype

Where Field1 = 'x' and sumRTYPE>5;

DROP Table sum_rtype;

But i get an error "invalid expression" on the sum_rtype load. Anyone knows why?

Re: Using Group in the load script like aggr

Tue, 03 Mar 2015 11:51:55 GMT

I tried your statement, it does work though i do not know why.

Now i have two tables in which one is filtered but consists of only two fields and the other isnt but consists of all fields, how do i merge them in such a way that i get a table that is filtered and consists all the fields?

Re: Using Group in the load script like aggr

anbu1984 — Tue, 03 Mar 2015 12:25:47 GMT

Use Left join to join filtered table with the other table

sum_rtype:

Load * Where SumRTYPE > 5;

Load

ID,

Sum(RTYPE) As SumRTYPE

Resident xxx

Group By ID;

Left Join(sum_rtype)

Load * From Table;

Re: Using Group in the load script like aggr

Tue, 03 Mar 2015 13:16:05 GMT

That does work, but it shows me all ID's where all RTYPES of any field1 are >5.

I just want the sum of RTYPES where field = 'x' > 5.

I tried

(DATEITYP = 'R' and field1= 'x',1,0) as RTYPE

and that seems to be working, but the sum is totally weird.

When i look at the table i get i.e. 5 entries with only one entry showing a 1 at the field RTYPE.

A sum(RTYPE) keeps returning 8 though, I don't understand why.

To be clear, i commented everyhting but the first load with the if condition to debug.

Re: Using Group in the load script like aggr

anbu1984 — Tue, 03 Mar 2015 13:27:09 GMT

sum_rtype:

Load * Where SumRTYPE > 5;

Load

ID,

Sum(RTYPE) As SumRTYPE

Resident xxx

Where DATEITYP = 'R' and field1= 'x'

Group By ID;

Re: Using Group in the load script like aggr

Tue, 03 Mar 2015 13:34:20 GMT

That would probably work, too.

Including it in the if flag above worked also

Thank you!