Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

inner join between two tables

Hi everyone,

       i need to load a new qvw data with information from two tables, something like this but in a qlikview file:

"select users.username, users.email  from users inner join deals

on deals.seller_id=users.id

group by users.email"

i tried to write something like that in qlikview but i had an error loading the qvw.

Could you give an advice?

The qlikview file look like this without the condition i wrote before:

SELLERS:

LOAD `available_balance_amount`,

    `available_points`,

    `bac_tourism_id`,

    `bac_user_id`,

    `blocked_amount`,

    `cookie_hash`,

    `cookie_time_modified`,

    created,

    email,

    `fb_user_id`,

    `fibertel_user_id`,

    `gift_user_id`,

    `has_buyed`,

    id,

    `is_active`,

    `is_agree_terms_conditions`,

    `is_email_confirmed`,

    `is_openid_register`,

    `last_logged_in_time`,

    `last_login_ip`,

    modified,

    password,

    `referred_by_user_id`,

    `referred_date`,

    `signup_ip`,

    `twitter_access_key`,

    `twitter_access_token`,

    `twitter_user_id`,

    `user_login_count`,

    `user_openid_count`,

    `user_type_id`,

    `user_view_count`,

    username,

    `wallet_blocked`;

SQL SELECT `available_balance_amount`,

    `available_points`,

    `bac_tourism_id`,

    `bac_user_id`,

    `blocked_amount`,

    `cookie_hash`,

    `cookie_time_modified`,

    created,

    email,

    `fb_user_id`,

    `fibertel_user_id`,

    `gift_user_id`,

    `has_buyed`,

    id,

    `is_active`,

    `is_agree_terms_conditions`,

    `is_email_confirmed`,

    `is_openid_register`,

    `last_logged_in_time`,

    `last_login_ip`,

    modified,

    password,

    `referred_by_user_id`,

    `referred_date`,

    `signup_ip`,

    `twitter_access_key`,

    `twitter_access_token`,

    `twitter_user_id`,

    `user_login_count`,

    `user_openid_count`,

    `user_type_id`,

    `user_view_count`,

    username,

    `wallet_blocked`

FROM clubcupon.users;

STORE SELLERS INTO ..\QVD\SELLERS.QVD;

DROP TABLE SELLERS;

Thanks

gerardo

1 Solution

Accepted Solutions
Not applicable

Re: inner join between two tables

Hi, this help me to resolve my issue.

SELLERS:

Load id as seller_id,

username,

email;

SQL SELECT DISTINCT `id`,

username,

email

From clubcupon.users;

INNER JOIN

Load seller_id;

SQL SELECT `seller_id`

FROM clubcupon.deals;

STORE SELLERS INTO ..\QVD\SELLERS.QVD;

DROP TABLE SELLERS;

6 Replies
celambarasan
Not applicable

Re: inner join between two tables

Hi,

     Have a common fieldname in both tables which only enable you to join a 2 tables.

deals:

Load

     *

From..;\\Already you have loaded

users:

Load

*

from.. ; \\Already you have loaded

OutputTable:

Load Distinct

          id,

          username,

          email

Resident

     users;

inner join(OutputTable)

Load

         seller_id as id

Resident

deals;

Now output table has the required data.

Hope it helps

Celambarasan

Not applicable

Re: inner join between two tables

Hi Celambarasan,

I tried to do what you said but this give me all users_id without this condition clubcupon.users.id = clubcupon.deals.seller_id

This is my new load script. Any idea?

DEALS:

Load `seller_id`;

SQL SELECT `seller_id`

FROM clubcupon.deals;

USERS:

LOAD `available_balance_amount`,

`available_points`,

`bac_tourism_id`,

`bac_user_id`,

`blocked_amount`,

`cookie_hash`,

`cookie_time_modified`,

created,

email,

`fb_user_id`,

`fibertel_user_id`,

`gift_user_id`,

`has_buyed`,

id,

`is_active`,

`is_agree_terms_conditions`,

`is_email_confirmed`,

`is_openid_register`,

`last_logged_in_time`,

`last_login_ip`,

modified,

password,

`referred_by_user_id`,

`referred_date`,

`signup_ip`,

`twitter_access_key`,

`twitter_access_token`,

`twitter_user_id`,

`user_login_count`,

`user_openid_count`,

`user_type_id`,

`user_view_count`,

username,

`wallet_blocked`;

SQL SELECT `available_balance_amount`,

`available_points`,

`bac_tourism_id`,

`bac_user_id`,

`blocked_amount`,

`cookie_hash`,

`cookie_time_modified`,

created,

email,

`fb_user_id`,

`fibertel_user_id`,

`gift_user_id`,

`has_buyed`,

id,

`is_active`,

`is_agree_terms_conditions`,

`is_email_confirmed`,

`is_openid_register`,

`last_logged_in_time`,

`last_login_ip`,

modified,

password,

`referred_by_user_id`,

`referred_date`,

`signup_ip`,

`twitter_access_key`,

`twitter_access_token`,

`twitter_user_id`,

`user_login_count`,

`user_openid_count`,

`user_type_id`,

`user_view_count`,

username,

`wallet_blocked`

FROM clubcupon.users;

SELLERS:

Load Distinct

id,

username,

email

Resident USERS;

inner join(SELLERS)

Load seller_id as id

Resident DEALS;

STORE SELLERS INTO ..\QVD\SELLERS.QVD;

De: Celambarasan Adhimulam

Enviado el: miércoles, 29 de febrero de 2012 09:18 a.m.

Para: Pauza, Gerardo

Asunto: - Re: inner join between two tables

QlikCommunity <http://community.qlik.com/index.jspa>

Re: inner join between two tables

created by Celambarasan Adhimulam <http://community.qlik.com/people/celambarasan> in New to QlikView - View the full discussion <http://community.qlik.com/message/196612#196612

celambarasan
Not applicable

Re: inner join between two tables

Hi,

     Check with this

    

SELLERS:

Load Distinct seller_id as id

Resident DEALS;

inner join(SELLERS)

Load Distinct

          id,

          username,

          email

Resident USERS;

Celambarasan

Not applicable

Re: inner join between two tables

I still have a problema…..

Should be have the same values….

De: Celambarasan Adhimulam

Enviado el: miércoles, 29 de febrero de 2012 10:49 a.m.

Para: Pauza, Gerardo

Asunto: - Re: inner join between two tables

QlikCommunity <http://community.qlik.com/index.jspa>

Re: inner join between two tables

created by Celambarasan Adhimulam <http://community.qlik.com/people/celambarasan> in New to QlikView - View the full discussion <http://community.qlik.com/message/196664#196664

Not applicable

Re: inner join between two tables

Hi, someone could help me with this?

thanks

gerardo

Not applicable

Re: inner join between two tables

Hi, this help me to resolve my issue.

SELLERS:

Load id as seller_id,

username,

email;

SQL SELECT DISTINCT `id`,

username,

email

From clubcupon.users;

INNER JOIN

Load seller_id;

SQL SELECT `seller_id`

FROM clubcupon.deals;

STORE SELLERS INTO ..\QVD\SELLERS.QVD;

DROP TABLE SELLERS;