inner join between two tables

[#1]

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

[CELAMBARASAN]

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

[#3]

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]

Hi,

     Check with this

SELLERS:

Load Distinct seller_id as id

Resident DEALS;

inner join(SELLERS)

Load Distinct

          id,

          username,

          email

Resident USERS;

Celambarasan

[#5]

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

[#6]

Hi, someone could help me with this?

thanks

gerardo

[#7 · Accepted solution]

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;