Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Using Group in the load script like aggr


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

Tags (2)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Using Group in the load script like aggr

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;

8 Replies
chrismarlow
Contributor III

Re: Using Group in the load script like aggr

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.

anbu1984
Honored Contributor III

Re: Using Group in the load script like aggr

sum_rtype:

Load * Where SumRTYPE > 5;

Load

     ID,

     Sum(RTYPE) As SumRTYPE

Resident xxx

Group By ID;

Not applicable

Re: Using Group in the load script like aggr

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?

Not applicable

Re: Using Group in the load script like aggr

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?

anbu1984
Honored Contributor III

Re: Using Group in the load script like aggr

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;

Not applicable

Re: Using Group in the load script like aggr

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

if

(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.

anbu1984
Honored Contributor III

Re: Using Group in the load script like aggr

sum_rtype:

Load * Where SumRTYPE > 5;

Load

     ID,

     Sum(RTYPE) As SumRTYPE

Resident xxx

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

Group By ID;

Not applicable

Re: Using Group in the load script like aggr

That would probably work, too.

Including it in the if flag above worked also

Thank you!