7 Replies Latest reply: Nov 28, 2017 9:26 AM by Jonnie Yacoub RSS

    Inner join?

    Jonnie Yacoub

      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?