topic Re: Left join not working? in QlikView

Left join not working?

Anonymous — Mon, 12 Mar 2018 11:13:52 GMT

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";

Re: Left join not working?

sunny_talwar — Mon, 12 Mar 2018 11:24:25 GMT

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

Re: Left join not working?

Anonymous — Mon, 12 Mar 2018 13:11:13 GMT

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?

sunny_talwar — Mon, 12 Mar 2018 13:12:04 GMT

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

Re: Left join not working?

gerry_hdm — Mon, 12 Mar 2018 13:23:38 GMT

you are not use

noconcatenate

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

Re: Left join not working?

Anonymous — Mon, 12 Mar 2018 13:25:07 GMT

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

Re: Left join not working?

Digvijay_Singh — Mon, 12 Mar 2018 21:36:21 GMT

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?

Re: Left join not working?

Anonymous — Mon, 19 Mar 2018 16:12:37 GMT

yes i'm seeing deal_id without lead_id...

Re: Left join not working?

dwforest — Tue, 20 Mar 2018 23:00:40 GMT

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