How to do a group by with expressions?

[#1]

here is query, how do you thing, wich expression tools it could be done

select count(distinct idGoods) as cnt, idDoc from drSales where idDoc in (select id from docJournal where opdate >= '2008-10-10' and opDate < '2008-10-11') group by idDoc

ho to do a group by in expression where is subquery

thanks

[#2]

There is nothing wrong with the SQL statement that you've entered here, that I can see.

What exactly is your question?

[prabhu0505]

Following is the code that I've tried in my hands-on :

load
`Employee ID`,
`Database` as [Database5],
sum(Experience) as Db_Exp
resident
Table1 where Database<>'-' group by `Employee ID`, `Database`;

In my view, there is no problem with query, check your data and fields.

[#4]

I mean, that i'm loading 5 tables with 100000 to 10 000 000 rows in each table.

if i will load new data by this sql statement - it would be double data in cash. That's why - I have got info from 5 tables by first loading and thhan i guess i should manipulate this data by tools in qlikview - like expression, but not to execute another pne query with joins. Am i right or not?

[johnw]

Not sure if I'm following. If you're saying that, for instance, you've already got a load of docJournal, then yes, it would be inefficient to repeat that load here.

The normal approach would be to create QVDs for your two tables. Then you would do the load from the QVDs. Something like this (untested):

[Table1]:
LOAD * INLINE [
opDate
2008-10-10
2008-10-11
];
INNER JOIN ([Table1])
LOAD
"id" as "idDoc"
,"opDate"
FROM docJournal.qvd (QVD)
WHERE exists("opDate") // done as exists instead of date check for optimized load
;
[Table2]:
LOAD
"idDoc"
,count(distinct idGoods) as cnt
FROM drSales.qvd (QVD)
WHERE exists("idDoc")
;
DROP TABLE [Table1];

[#6]

Tkank u, for ur response.))

I undestand now that for getting real information i need from tables i should write queries and load result sets with that queries. But what is the language?(very similar to sql), and wre can i get tutorials to learn this language? thank u.