Key construction - Help with script

[OmarBenSalem]

Hi all, 

I'm facing a problem joining 2 tables and I'd be appreciative if someone would help ( @sunny_talwar )

Here's what I have :

1) 1 Table from Oracle

2) 1 Table from excel

The 2 tables contain the same fields and I want to compare the differences btwn both files...

Here's what I did:

Qualify * ;
Unqualify Key;
Excel:
load *, 1 as Flag
,(trim("Service")
&'|'&
trim("Emploi du responsable")&'|'&
trim("Délégation Régionale") &'|'&
trim("Site Apec")&'|'&

trim( "Niveau 1")&'|'&
trim( "Service MSP (O/N)")&'|'&
trim( "Valo Eco MSP (O/N)")&'|'&

trim("Date de réalisation du service")) as Key;
LOAD
"Service",
date( "Date de réalisation du service") as "Date de réalisation du service",
sum( "Volume de services réalisés") as "Volume de services réalisés",
sum( "Temps de production") as "Temps de production",
"Délégation Régionale",
SubField("Site Apec",' ',2) as "Site Apec",
"Emploi du responsable",
"Niveau 1",
"Service MSP (O/N)",
"Valo Eco MSP (O/N)",
sum( "Volume de services réalisés MSP") as "Volume de services réalisés MSP",
sum( "Valo Eco aux temps réels MSP") as "Valo Eco aux temps réels MSP"
FROM [lib://DATA_SOURCES/Extract STAD - 2018 - Semaine 39 V02 20181221 - Copie.xlsx]
(ooxml, embedded labels, table is SERVICES) group by "Service","Date de réalisation du service", "Délégation Régionale","Site Apec"
,"Emploi du responsable",
"Niveau 1",
"Service MSP (O/N)",
"Valo Eco MSP (O/N)";

LIB CONNECT TO 'DWH_SOCLE_INT';

Qlik:

load *,1 as Flag
,(trim("Service")
&'|'&
trim("Emploi du responsable")&'|'&
trim("Délégation Régionale") &'|'&
trim("Site Apec")&'|'&

trim( "Niveau 1")&'|'&
trim( "Service MSP (O/N)")&'|'&
trim( "Valo Eco MSP (O/N)")&'|'&

trim("Date de réalisation du service")) as Key;
;

LOAD

TDBH_SRV_SERVICE as "Service",
TDBH_SRV_EMPLOI_RESP as "Emploi du responsable",
TDBH_SRV_DELEG_REG as "Délégation Régionale",
TDBH_SRV_SIT_APEC as "Site Apec",
sum(TDBH_SRV_HEURE_PROD) as "Temps de production",
sum(TDBH_SRV_VOL_SERV_REAL) as "Volume de services réalisés",
TDBH_SRV_NIV1 as "Niveau 1",
TDBH_SRV_SRV_MSP as "Service MSP (O/N)",
TDBH_SRV_VALOECO_MSP as "Valo Eco MSP (O/N)",
sum(TDBH_SRV_VOL_SERV_REAL_MSP) as "Volume de services réalisés MSP",
sum(TDBH_SRV_VALOECO_REEL_MSP) as "Valo Eco aux temps réels MSP",
date(TDBH_SRV_DAT_REA_SERV) as "Date de réalisation du service"
group by
TDBH_SRV_SERVICE ,TDBH_SRV_DAT_REA_SERV,TDBH_SRV_DELEG_REG,TDBH_SRV_SIT_APEC,TDBH_SRV_EMPLOI_RESP,TDBH_SRV_NIV1,TDBH_SRV_SRV_MSP,TDBH_SRV_VALOECO_MSP

;

SELECT "TDBH_SRV_SEMAINE",
"TDBH_SRV_SEMAINE_TDB",
"TDBH_SRV_ANNEE",
"TDBH_SRV_ID_SERVICE",
"TDBH_SRV_SERVICE",
"TDBH_SRV_ID_EMPLOI_RESP",
"TDBH_SRV_EMPLOI_RESP",
"TDBH_SRV_ID_DELEG_REG",
"TDBH_SRV_DELEG_REG",
"TDBH_SRV_ID_SIT_APEC",
"TDBH_SRV_SIT_APEC",
"TDBH_SRV_HEURE_PROD",
"TDBH_SRV_VOL_SERV_REAL",
"TDBH_SRV_NIV1",
"TDBH_SRV_SRV_MSP",
"TDBH_SRV_VALOECO_MSP",
"TDBH_SRV_VOL_SERV_REAL_MSP",
"TDBH_SRV_VALOECO_REEL_MSP",
"TDBH_SRV_DAT_REA_SERV",
"TDBH_SRV_DAT_ALI"
FROM "DWHSOCLE"."TDB_HEBDO_SERVICE" where TDBH_SRV_SEMAINE='39' and TDBH_SRV_ANNEE='2018';

I want to "join" the 2 tables by The Key field (I did not use the autonumber function so that u can see the Key):

Result:

 See the first line in both tables: it's the same and the Key is also the same

=> if I select this Key, normally, I should have both lines in both tables kept.

But, this is not the case:

each table has its own key which is not linked to the same key of the second table:

With that, If I select the Key in table one, it will import the null values of table 2:

 

What am I doing wrong here?

[Jacek]

Hi, let's check just add another field with autonumber for this TEXT key and compare that ID for field is the same for both tables or not. Maybe coding for selected character is issue here? Also you can create a special key: DUAL( TEXT_KEY , Autonumber(TEXT_KEY)) as KEY

[sunny_talwar]

Last part of your key is a date field.... it might or might not be the same... in order to force them to be the same... try using this

Trim(Text("Date de réalisation du service")) 

Or this

Trim(Num(Floor("Date de réalisation du service")))

and see if that makes any difference..

[OmarBenSalem]

Yes indeed; that's the problem; the autonumber does not generate the same ID for the same Key..

While the components of the key are the same and I'm trying to format each field of both tables with same logic..

But it won't budge..

[OmarBenSalem]

I've formated both date fields in both tables the same way; and it still does not change..

[sunny_talwar · Accepted solution]

They look the same...can you break the key into different pieces and see which component is causing the issue? I would start with just this to begin with

Trim("Service") as Key

and if that works, add the second part

Trim("Service")&'|'&Trim("Emploi du responsable") as Key

and if that works add another one...

This way you will know which field is breaking the key... 

[OmarBenSalem · Accepted solution]

Itw as the Niveau1 field (sécurisation des recrutements des entreprises)...

I changed it as follow:

trim(subfield(Niveau1,' ',-1)) and now it works ! 

[sunny_talwar]

Great!! I am glad you were able to fix it 🙂