Check values in different tables

[Anonymous]

Hi guys,

I need to count the number of users (emails) from specific table and field but with a condition.

Exemple:

            

What i want is if(users (table1) exist on Followers (table2), count users (table 1).

My idea, is create a resident table on script like:

Users:

Load

Date(floor(Date),'YYYYMMDD') AS date_id,

if(users=followed_user_id or user_id=follower_user_id, count(user_id)) as UC

resident Facts;      (Facts is the name from the concatenation the tables that i have on database)

Drop Table Users;

Anyone have other ideia to do this?

Thanks in advance,

Pedro Lopes

Resident load‌

Count Distinct combination of 2 or more fields‌

How to compare 2 tables and put non-matching results?‌

@swuehl

[kaushiknsolanki]

Hi,

What you need is the exists function.

Load Followers from table 2;

Load Users from Table 1 where exists(Followers,Users);

So the second table will load only users which are there in table 1.

Then you can count the users from Table 1;

Regards,

Kaushik Solanki

[Anonymous]

Hi Kaushik,

Thanks for your response

How apply this method on my script:

Facts:

LOAD

    date,

    user_id

FROM [lib://DMP (qliksense_administrator)/DMP_1\users_*.qvd] (qvd);

concatenate

LOAD

    date,

    user_id

FROM [lib://DMP (qliksense_administrator)/DMP_2\users_*.qvd] (qvd);

concatenate

LOAD

    date,

    followed_user_id,

    follower_user_id

FROM [lib://DMP (qliksense_administrator)/Social\Connections\Connections_*.qvd] (qvd);

Concatenate

LOAD

    date,

    sharer_user_id

FROM [lib://DMP (qliksense_administrator)/Social\Shares\Shares_*.qvd] (qvd);

I want 4 specific values:

1 - if(user_id exist on followed_user_id and follower_user_id, count(user_id) as LUC

2 - if(user_id exist on followed_user_id , count(user_id) as UC_ed

3 - if(user_id exist on follower_user_id , count(user_id) as UC_er

4 - if(user_id exist on sharer_user_id , count(user_id) as SUC

It's possible create this new fields in other table? Because i still continue have the original fields.

Sorry my giant question, but i can't see a simple way to do this.

Thanks in advance,

Pedro Lopes

[Anonymous]

Please, anyone can help me?

[rahulpawarb · Accepted solution]

Hello Pedro,

Trust that you are doing good!

Please refer below given sample script for your reference:

Map_Shares:
Mapping LOAD
sharer_user_id AS user_id,
1 AS Cnt;
LOAD * INLINE [
Date, sharer_user_id, Country, Value
01/01/2017, Jack@, IN, 1000
01/01/2017, Joe@, IN, 3000
];

Connections:
LOAD * INLINE [
Date, followed_user_id, follower_user_id, Country, Value
01/01/2017, Jack@, Jack@, IN, 1000
01/01/2017, Jinny@, Jack@, IN, 2000
];

Map_Connections_er:
Mapping LOAD
follower_user_id AS user_id,
1 AS Cnt
Resident Connections;

Map_Connections_ed:
Mapping LOAD
followed_user_id AS user_id,
1 AS Cnt
Resident Connections;

DROP Table Connections;

Users:
LOAD * INLINE [
Date, user_id, Country, Value
01/01/2017, Jack@, IN, 1000
01/01/2017, Jinny@, IN, 2000
01/01/2017, Joe@, IN, 3000
];

FinalUserCount:
LOAD
user_id,
Sum(If(ApplyMap('Map_Connections_er', user_id, 0)=1 AND ApplyMap('Map_Connections_ed', user_id, 0)=1, 1,0)) AS LUC,
Sum(ApplyMap('Map_Connections_ed', user_id, 0)) AS UC_ed,
Sum(ApplyMap('Map_Connections_er', user_id, 0)) AS UC_er,
Sum(ApplyMap('Map_Shares', user_id, 0)) AS SUC
Resident Users
GROUP BY user_id;

Hope that it will be helpful.

Regards!

Rahul

[Anonymous]

Hi Rahul Pawar,

Thanks for your response.

I have some questions.

1 - In my case when i make a mapping

Map_shares:

mapping LOAD

sharer_user_id as user_id,

1 as Cnt

From .....qvd

I don't need to do the load in line (i read from my table shares)? Right?

From the connections table, i read the table and after a make some mappings and i use the resident table? Right?

After i drop the table.

2- In the final user count, i use sum or count? In reality, i want to count de users.

Thanks in advance,

Pedro Lopes

[rahulpawarb]

Hello Pedro,

Please see my comments:

1 - In my case when i make a mapping

Map_shares:

mapping LOAD

sharer_user_id as user_id,

1 as Cnt

From .....qvd

I don't need to do the load in line (i read from my table shares)? Right?

You can write a mapping load script to read data from table. It should work without any issues.

From the connections table, i read the table and after a make some mappings and i use the resident table? Right?

After i drop the table.

For Connections table, we need to have two different mapping tables - 1. For follower_user_id and 2. For followed_user_id

You can achieve it using resident load and post that drop the Connections table.

2- In the final user count, i use sum or count? In reality, i want to count de users.

If you see the logic applied, If a given user_id is present in Connections or Shares table then ApplyMap function will return 1 else 0. Therefore, we using sum instead of count.

Hope this will be helpful.

Regards!

Rahul

[Anonymous]

Thanks for your support Rahul Pawar,

Works very well

Best Regards,

Pedro Lopes

[rahulpawarb]

Cheers,

Rahul