Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jonnieyacoub
Contributor

Left join not working?

Hi Qlikkers,

So i'm trying to LEFT JOIN my table deal on lead, but its not giving me back the result i want... I want to visualisize all the leads and deals that have the same lead_id on one line so i can use one date as dimension and 2 counts (lead & deal)

LIB CONNECT TO 'lead (-----------)';

[lead]:

Load

     [id] AS lead_id,

    DATE(inserted, 'DD/MM/YYYY') as lead_inserted,

    [name_last] AS lead_name_last,

    [priority] AS lead_priority,

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

    [owner_user] as user_id;

SQL SELECT

(SELECT

         "id",

        "inserted",

        "name_last",

        "priority",

        "source",

        "owner_user"

FROM "root")

FROM JSON (wrap on) "root";

LIB CONNECT TO 'deal (-------------)';

LEFT JOIN

[deal]:

Load

     [id] AS deal_id,

    DATE(inserted, 'DD/MM/YYYY') as deal_inserted,

    [lead] AS lead_id,

    [office] AS deal_office,

    [owner_user] as userid,

    [value_cents] AS deal_value_cents;

SQL SELECT

(SELECT

         "id",

        "inserted",

        "lead",

        "office",

        "owner_user",

        "value_cents"

FROM "root")

FROM JSON (wrap on) "root";

8 Replies

Re: Left join not working?

Can you do a JOIN instead of LEFT JOIN to see if the lead_id from the two fields align or not?

jonnieyacoub
Contributor

Re: Left join not working?

i have tried all the joins. If i do an INNER JOIN they align in a row, but then i miss all the leads that have not became deals... With the JOIN or LEFT JOIN i have values in the column deal_id, deal_inserted etc. But they are not in one single row...

Re: Left join not working?

That is strange... can you show this via images of what you see when you join them using different joins?

gerry_hdm
Contributor II

Re: Left join not working?

you are not use

noconcatenate

is this steps two indentity tables then you see nur one Table

jonnieyacoub
Contributor

Re: Left join not working?

unfortunately i cannot show you images.. Maybe it is an Qlik Sense Cloud bug or some kind?

Digvijay_Singh
Honored Contributor III

Re: Left join not working?

With left join are you seeing deal_Id without lead_Id? So you are saying you see records with no lead_id? Do you have records in lead table with blank or nulls?

jonnieyacoub
Contributor

Re: Left join not working?

yes i'm seeing deal_id without lead_id...

dwforest
Valued Contributor

Re: Left join not working?

are deal_id and lead_id the defined as the same data type?

maybe lead_id is a string or integer and id is a double?

Qlik joins based on actual value of a field not displayed value, so 1 <> 1.0 and ' 1' <> 1

Community Browser