topic Re: join problem : only one reccord with several tables in QlikView

join problem : only one reccord with several tables

Mon, 19 Nov 2012 13:15:33 GMT

Hello,

I need your help for a join problem...

I load data from excel sheet (domaines1) and after that data from oracle base (domaines2).

the same ID [N Interaction]could be int he both sources (excel sheet and oracle table), and i want to put the value of the excel sheet if and only if the Id of the excel shheet exists in the oracle table. I don't want to records for the same id.

I made theses scripts and i tried group by, but it doesn't work too....

Anyone has an idea?

domaines1:
LOAD Domaine,
     [Flux - Objet impacté],
     Demandeur,
     [Interaction Generix] as [N Interaction]
FROM
domaine.xlsx
(ooxml, embedded labels);

domaines2:
Load
     I_ID as [N Interaction],
     GEN_CUSTOMER_REFERENCE as [Référence client]
     ;
sql select
     i.incident_id as I_ID,
     substr(i.gen_customer_reference||chr(10),0,5)as GEN_CUSTOMER_REFERENCE
from
     "SM_27_PRD_L".incidentsm1;

join
load

     Domaine as [Référence client],
    [Flux - Objet impacté],
    Demandeur,
    [N Interaction]
resident domaines1 ;

drop table domaines1;

Re: join problem : only one reccord with several tables

Gysbert_Wassenaar — Mon, 19 Nov 2012 13:28:03 GMT

Try using a right join to keep all records from domaines2 and only those from domaines1 that have a matching record in domaines2

domaines1:

LOAD

    Domaine as [Référence client],
    [Flux - Objet impacté],
    Demandeur,
    [Interaction Generix] as [N Interaction]
FROM
domaine.xlsx
(ooxml, embedded labels);

domaines2:
right join Load
     I_ID as [N Interaction],
     GEN_CUSTOMER_REFERENCE as [Référence client]
     ;
sql select
     i.incident_id as I_ID,
     substr(i.gen_customer_reference||chr(10),0,5)as GEN_CUSTOMER_REFERENCE
from
     "SM_27_PRD_L".incidentsm1;

Re: join problem : only one reccord with several tables

Mon, 19 Nov 2012 13:46:06 GMT

Sorry gysbert

but i've got only the id from domaines2 and none from domaines1