topic Re: How do you script a multi table GROUP BY from QVD's? in QlikView

How do you script a multi table GROUP BY from QVD's?

Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

In SQL you will have something like this:

SELECT T1.A, T2.B, MIN(T3.x), MAX(T3.Y)

FROM T1

INNER JOIN T2 on T1.KEY = T2.KEY

INNER JOIN T3 on T2.KEY = T3.KEY

GROUP BY A, B

How do i script this in Qlikview?

I can add a GROUP BY to single table but how do a add a GROUP BY for the query as a whole?

Re: How do you script a multi table GROUP BY from QVD's?

sunny_talwar — Thu, 02 Feb 2017 15:54:44 GMT

May be like this:

T1:

LOAD Key,

FROM T1;

Inner Join (T1)

LOAD Key,

FROM T2;

Inner Join (T1)

LOAD Key,

FROM T3;

Final:

LOAD A,

Min(X) as X,

Max(Y) as Y

Resident T1

Group By A, B;

DROP Table T1;

Re: How do you script a multi table GROUP BY from QVD's?

Anonymous — Thu, 02 Feb 2017 16:54:17 GMT

You can write it this way like a normal sql the only thing you need is to proceed the SELECT with Keyword SQL. Provided that you have the connection setup.

Table:
SQL
SELECT
T1.A, T2.B, MIN(T3.x), MAX(T3.Y)

FROM T1

INNER JOIN T2 on T1.KEY = T2.KEY

INNER JOIN T3 on T2.KEY = T3.KEY

GROUP BY A, B;

if you want to do further processing to the result you can add as many layers of load as you want.

Example:

Table://layer 1

Load *;

SQL
SELECT
T1.A, T2.B, MIN(T3.x), MAX(T3.Y)

FROM T1

INNER JOIN T2 on T1.KEY = T2.KEY

INNER JOIN T3 on T2.KEY = T3.KEY

GROUP BY A, B;