topic Re: inner join between two tables in QlikView

inner join between two tables

Wed, 29 Feb 2012 12:06:12 GMT

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

Re: inner join between two tables

CELAMBARASAN — Wed, 29 Feb 2012 12:17:56 GMT

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,

Resident

users;

inner join(OutputTable)

Load

seller_id as id

Resident

deals;

Now output table has the required data.

Hope it helps

Celambarasan

Re: inner join between two tables

Wed, 29 Feb 2012 13:42:07 GMT

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,

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

Re: inner join between two tables

CELAMBARASAN — Wed, 29 Feb 2012 13:48:20 GMT

Hi,

Check with this

SELLERS:

Load Distinct seller_id as id

Resident DEALS;

inner join(SELLERS)

Load Distinct

id,

username,

Resident USERS;

Celambarasan

Re: inner join between two tables

Wed, 29 Feb 2012 14:19:22 GMT

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

Re: inner join between two tables

Wed, 29 Feb 2012 17:27:20 GMT

Hi, someone could help me with this?

thanks

gerardo

Re: inner join between two tables

Thu, 01 Mar 2012 19:05:10 GMT

Hi, this help me to resolve my issue.

SELLERS:

Load id as seller_id,

username,

email;

SQL SELECT DISTINCT `id`,

username,

From clubcupon.users;

INNER JOIN

Load seller_id;

SQL SELECT `seller_id`

FROM clubcupon.deals;

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

DROP TABLE SELLERS;