Qlik Community

Qlik Sense Cloud Discussions

jonnieyacoub
Contributor

Inner join?

Hey Qlikers,

i'm trying to make an inner join on 2 tables. I would like to have an table with the colums: [lead_id], [deal_id], [lead_inserted], [deal_inserted], [lead_priority], [lead_source], [owner_user], [type]. I have the following tables:

LIB CONNECT TO 'lead (qlikcloud_qlikid_jonnieyacoub)';

[lead]:

Load

     [id] AS lead_id,

    DATE([inserted], 'DD/MM/YYYY') AS [lead_inserted],

    YEAR([inserted]) AS [lead_year],

    MONTH([inserted]) AS [lead_month],

    DAY([inserted]) AS [lead_day],

     [client] AS lead_client,

    [name_last] AS lead_name_last,

    [priority] AS lead_priority,

    [status_followup_date] AS lead_status_followup_date,

    [status_followup_time] AS lead_status_followup_time,

    [status_followup_description] AS lead_status_followup_description,

    ApplyMap('lead_sourceMap',[source], null()) AS source,

    [owner_user] AS user_id,

    TEXT('lead') AS lead_type;

SQL SELECT

(SELECT

         "id",

        "inserted",

         "client",

        "name_last",

        "priority",

        "status_followup_date",

        "status_followup_time",

        "status_followup_description",

        "source",

        "owner_user"

FROM "root")

FROM JSON (wrap on) "root";

LIB CONNECT TO 'deal (qlikcloud_qlikid_jonnieyacoub)';

[deal]:

Load

     [id] AS deal_id,

    DATE([inserted], 'DD/MM/YYYY') AS [deal_inserted],

    YEAR([inserted]) AS [deal_year],

    MONTH([inserted]) AS [deal_month],

    DAY([inserted]) AS [deal_day],

    [lead] AS lead_id,

    [office] AS deal_office,

    [owner_user] AS deal_owner_user,

    [value_cents] AS deal_value_cents,

    [status_added] AS deal_status_added,

    [status_converted] AS deal_status_converted,

    [status_invoiced] AS deal_status_invoiced,

    [status_lost] AS deal_status_lost,

    TEXT('deal') AS deal_type;

SQL SELECT

(SELECT

         "id",

        "inserted",

        "lead",

        "office",

        "owner_user",

        "value_cents",

        "status_added",

        "status_converted",

        "status_invoiced",

        "status_lost"

FROM "root")

FROM JSON (wrap on) "root";

any idea how to get this new table?

1 Solution

Accepted Solutions
MVP
MVP

Re: Inner join?

You should put Inner Join in front of your second load statement on the line after [deal]:

7 Replies
Ralf_Heukäufer
Contributor III

Re: Inner join?

You can use a left join by one of the tables

like

left join (lead)

over the load statement of table two.

If you want a extra table with only the columns you write you can make a resident load of both tables with only the columns you need and make a left join after that.

jonnieyacoub
Contributor

Re: Inner join?

And do you know how that would look like? I have no clue where to type the LEFT JOIN or INNER JOIN syntax...

MVP
MVP

Re: Inner join?

You should put Inner Join in front of your second load statement on the line after [deal]:

Ralf_Heukäufer
Contributor III

Re: Inner join?

jonnieyacoub
Contributor

Re: Inner join?

Yes thank you! i know now to to make joins. An inner join is sort of an half solution. When i use this join i lose all the LEADS where there is no DEAL. and i need these fields for counting. So i did an LEFT JOIN, but now i have lost all the data of the table [deal]:. Any idea on how to fix this?

Re: Inner join?

Do an OUTER JOIN to retain all the rows.

-Rob

jonnieyacoub
Contributor

Re: Inner join?

yesss almost! This solved my date dimension. But now i cannot filter on [priority]. For example i want to know how many deals are made with the [lead_priority] on high... Same issue with [lead_source]

So i think its best to make a new table with fields from table [lead] and [deal]. is it possible to make an inner join in a new table?

Community Browser