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

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

Fri, 04 Feb 2011 15:26:56 GMT

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.

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

vgutkovsky — Fri, 04 Feb 2011 18:20:54 GMT

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,

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

Mon, 07 Feb 2011 15:54:53 GMT

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?

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

vgutkovsky — Mon, 07 Feb 2011 16:04:25 GMT

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,

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

Tue, 08 Feb 2011 13:48:26 GMT

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.

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

vgutkovsky — Tue, 08 Feb 2011 16:34:00 GMT

Jai,

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

Regards,

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

vincent_ardiet — Tue, 08 Feb 2011 18:18:37 GMT

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

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

Wed, 09 Feb 2011 11:24:20 GMT

Hey Vincent,

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

jai

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

Wed, 09 Feb 2011 11:27:42 GMT

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.