Join two tables and do average function - SQL query provided

[Shifonjer]

Hi all,

I am loading two tables from excel file (both are in same file just different sheet) like this :

Table1:

LOAD BI,
Subject,
Grade
FROM
[Some_Excel.xlsx]
(ooxml, embedded labels, table is Ispiti);

Table2:

LOAD BI,
Name,
LastName,
Address,
City
FROM
[Some_Excel.xlsx]
(ooxml, embedded labels, table is Studenti);

What i want to do is same as this SQL Query 

Select City, AVG(Grade) as Average_Grade
from Table2
Join Table1 on Table1.BI = Table2.BI
Group By City
Oredr By Average_Grade;

Could anyone help me with this

[Rodj · Accepted solution]

If you determined to replicate a SQL like scenario for whatever reason you could go about it like this:

Table1_temp:

LOAD BI,

Subject,

Grade

FROM

[Some_Excel.xlsx]

(ooxml, embedded labels, table is Ispiti);

inner join

LOAD BI,

Name,

LastName,

Address,

City

FROM

[Some_Excel.xlsx]

(ooxml, embedded labels, table is Studenti);

Table1:

load City,

AVG(Grade) as Average_Grade

from table1_temp

Group By City

Order By Average_Grade;

drop table Table1_temp;

Depending on what you are trying to achieve however this may be totally unnecessary.

If what you want to get is a table or chart with the Avg Grade by city(or subject, or LastName etc) then all you need to do is load the two tables and Qlik Sense will automatically associate them on the BI field. This is the beauty of association, you don't need to define any joins etc and you won't be limiting the data you load as happens with SQL's set based logic. Then all you need to do is add a table object onto your sheet, add city as a dimension and then add avg(Grade) as a measure and you'll have the table you were trying to create.

Hope that's useful.

Rod

[Shifonjer]

Thanks this worked. I didn't know how to do this just using the table but now i know 😄

[Rodj]

Excellent! I wasn't quite sure of your reason for trying to do what you asked so I tried to cover both bases.

Cheers,

Rod