Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

hey all,

I am working on 3 different tables to form a temporary table and to load different fields to the table. When i am using the load statement with group by clause, it asks for aggregate function. i tried with aggregate functions of different fields and the error says invalid expression and the table is not loaded.

for example

tmptable:

SQL select table1.id,table1.name,table2.tyid,table2.age,table3.location from table 1

inner join table 2 on table1.id = table2.id

inner join table3 on table2.tyid = table3.tyid

where table1.name is not null

load id as id

count(typid) as numoftypeid¨

age as age

if(name ='abc',location) as currlocation

resident tmptable

group by id;

drop tmptable;

My problem is which field i should use the aggregate function . Because my data in the table comes with repetation of id with different field data.

i need to combine all the fields in one row with the same id.

can anyone help me with this?

Thanks

jai.

1 Solution

Accepted Solutions
Not applicable

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

hey Vlad,

Thanks once again for your help. i am posting the reduced version of the document.

tmpinfo:

SQL SELECT Objekt.ObjektId,Objekt.size,Objekt.noofroom,Roll.checkRoll,

substring(Person.firstname,1,15)+' '+substring(Person.surname,1,15) as PersonName

FROM Objekt

INNER JOIN ObjektIntressent

ON Objekt.ObjektId = ObjektIntressent.ObjektId

INNER JOIN Roll

ON ObjektIntressent.ObjektId = Roll.ObjektId

INNER JOIN Person

ON ObjektIntressent.PersonId = Person.PersonId

WHERE (Roll.checkRoll = '1' or Roll.checkRoll = '2' or Roll.checkRoll = '6');

Load

ObjektId,

Size,

Noofroom,

if (checkRoll = '1',PersonName,'') as Seller,

if (checkRoll = '2',PersonName,'') as Shopper,

if (checkRoll = '6',PersonName,'') as Mediator

Resident tmpinfo;

DROP TABLE tmpinfo;

If i load the table, i am getting the values like

ObjektId Size Noofroom Seller Shopper Mediator

1 10 2 adc

1 10 2 rew

1 10 2 wer

The data is written in 3 different rows. I tried left join with aggregation different data exccept the group by field. But getting invalid Expression.

Let me know if you need some more data.

Thanks for your help

Jai.

8 Replies
vgutkovsky
Honored Contributor II

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

Jai,

A QV group by requires that all fields (except the ones you're grouping by of course) be aggregated. That's why you're getting the error you mentioned. Try this instead:


final_data:
LOAD
id,
age,
if(name ='abc',location) as currlocation
resident tmptable;
LEFT JOIN LOAD
id,
count(typid) as numoftypeid
resident tmptable
group by id;
drop tmptable;


Regards,

Not applicable

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

hey Vlad,

Thanks for your reply. I implemented the way you suggested me.

But i am getting invalid expression or if i do aggregation with the different table field,i am getting the same data duplicated.

can you explain me in detail about the process of loading the data with inner join of 3 or more tables?

vgutkovsky
Honored Contributor II

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

I'm not sure what you mean, but you don't need to use any inner joins in your case. The code I wrote above should not be generating any errors, assuming you field names are correct. Please post a reduced version of your document and I'll take a look at the load script.

Regards,

Not applicable

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

hey Vlad,

Thanks once again for your help. i am posting the reduced version of the document.

tmpinfo:

SQL SELECT Objekt.ObjektId,Objekt.size,Objekt.noofroom,Roll.checkRoll,

substring(Person.firstname,1,15)+' '+substring(Person.surname,1,15) as PersonName

FROM Objekt

INNER JOIN ObjektIntressent

ON Objekt.ObjektId = ObjektIntressent.ObjektId

INNER JOIN Roll

ON ObjektIntressent.ObjektId = Roll.ObjektId

INNER JOIN Person

ON ObjektIntressent.PersonId = Person.PersonId

WHERE (Roll.checkRoll = '1' or Roll.checkRoll = '2' or Roll.checkRoll = '6');

Load

ObjektId,

Size,

Noofroom,

if (checkRoll = '1',PersonName,'') as Seller,

if (checkRoll = '2',PersonName,'') as Shopper,

if (checkRoll = '6',PersonName,'') as Mediator

Resident tmpinfo;

DROP TABLE tmpinfo;

If i load the table, i am getting the values like

ObjektId Size Noofroom Seller Shopper Mediator

1 10 2 adc

1 10 2 rew

1 10 2 wer

The data is written in 3 different rows. I tried left join with aggregation different data exccept the group by field. But getting invalid Expression.

Let me know if you need some more data.

Thanks for your help

Jai.

vgutkovsky
Honored Contributor II

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

Jai,

This is not a reduced version of the document...I meant a QVW. Very hard to tell anything from this...

Regards,

vincent_ardiet
Contributor III

Re. :Re: How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

Hi,

Perhaps what you need is a MaxString like this :

Load

ObjektId,

Size,

Noofroom,

MaxString(if (checkRoll = '1',PersonName,'')) as Seller,

MaxString(if (checkRoll = '2',PersonName,'')) as Shopper,

MaxString(if (checkRoll = '6',PersonName,'')) as Mediator

Resident tmpinfo

Group by ObjektId,

Size,

Noofroom;


You will obtain only 1 row :

1;10;2;adc;rew;wer

Vincent

Not applicable

Re. :Re: How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

Hey Vincent,

It works... Smiley Happy.. Thanks for your help.

jai

Not applicable

How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

Hey Vlad,

Since i am new to this qlikview, it is very hard with the qlikview terms.So i sent my query to get resolved. Now learning quite well i believe. Smiley Happy

Thanks for your support. Smiley Happy

jai.