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

[#1]

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.

[vgutkovsky]

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,

[#3]

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]

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,

[#5 · Accepted solution]

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]

Jai,

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

Regards,

[vincent_ardiet]

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

[#8]

Hey Vincent,

It works... :).. Thanks for your help.

jai

[#9]

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

Thanks for your support. 🙂

jai.