Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have two table F_details and F_Maroc and i need to have only one table (F_Details) and add the field Flag_Doublon in this table. To calculate the Flag doublon i need the PLAQUE (DATE_EMBARQUEMENT&'-'&PLAQUE_REMORQUE as PLAQUE) when i use the code in this discussion i have only the value 0 (please check the definiton of the field Flag_Doublon ) (if i use this code for example the Flag doublon work :
NoConcatenate
F_Maroc:
load
// DATE_EMBARQUEMENT ,
// PLAQUE_REMORQUE,
DATE_EMBARQUEMENT&'-'&PLAQUE_REMORQUE as PLAQUE,
if( Nombre_Carrier>1, 1, 0) as Flag_Doublon;
Load
DATE_EMBARQUEMENT ,
PLAQUE_REMORQUE,
count(CODE_CARRIER) as Nombre_Carrier
resident Fact_DW_Temp
Where CODE_LIGNE = 'MAR'
group by DATE_EMBARQUEMENT ,
PLAQUE_REMORQUE;
drop table Fact_DW_Temp;
)
And this is the code used now (Flag_Doublon = 0 )
NoConcatenate
Fact_DW_Temp:
LOAD NUM_DOSSIER,
CODE_CGV,
//NUM_VOYAGE,
//DATE_DOSSIER as Date_Dossier,
DATE_DOSSIER,
DATE_EMBARQUEMENT ,
//DATE_DEBARQUEMENT,
Parc,
CODE_LIGNE,
CODE_CARRIER,
Activite as activite,
//CODE_FILIALE_ORIGINE,
CODE_SENS,
CODE_COMPAGNIE_MARITIME,
PLAQUE_REMORQUE;
//CODE_FILIALE_DESTINATION,
// Code_TYPE_MATERIEL;
SELECT "NUM_DOSSIER",
"NUM_VOYAGE",
"CODE_SENS",
"CODE_COMPAGNIE_MARITIME",
"DATE_DOSSIER",
"CODE_LIGNE",
"CODE_CGV",
"CODE_CARRIER",
"DATE_EMBARQUEMENT",
"DATE_DEBARQUEMENT",
Parc,
Activite,
"CODE_FILIALE_ORIGINE",
"PLAQUE_REMORQUE",
"CODE_FILIALE_DESTINATION",
"Code_TYPE_MATERIEL"
FROM "VECTORYS_DATAWARHOUSE".DWH."DOSSIER_FAIT";
///////
NoConcatenate
F_Details:
Load
Date(DATE_DOSSIER, 'DD/MM/YYYY')&'-'&text(num(CODE_SENS))&'-'&text(num(CODE_COMPAGNIE_MARITIME))&'-'&text(activite)&'-'&text(CODE_LIGNE)&'-'&text(CODE_CARRIER)as [%Key ID],
autonumber( date(DATE_DOSSIER, 'DD/MM/YYYY'),'%Date ID') as [%Date ID],
// // CODE_COMPAGNIE_MARITIME,
AutoNumber(CODE_COMPAGNIE_MARITIME,'%CODE_COMPAGNIE_MARITIME') as [%CODE_COMPAGNIE_MARITIME],
// //CODE_SENS,
AutoNumber(CODE_SENS,'%CODE_SENS') as [%CODE_SENS],
CODE_CARRIER,
activite,
CODE_LIGNE,
CODE_CGV,
DATE_EMBARQUEMENT ,
PLAQUE_REMORQUE,
//DATE_EMBARQUEMENT&'-'&PLAQUE_REMORQUE as PLAQUE,
Parc,
Flag_Doublon;
load
DATE_DOSSIER,
CODE_CGV,
CODE_SENS,
CODE_COMPAGNIE_MARITIME,
CODE_CARRIER,
DATE_EMBARQUEMENT ,
PLAQUE_REMORQUE,
Parc,
CODE_LIGNE,
activite,
if( Nombre_Carrier>1, 1, 0) as Flag_Doublon;
LOAD
DATE_DOSSIER,
CODE_CGV,
CODE_SENS,
CODE_COMPAGNIE_MARITIME,
CODE_CARRIER,
DATE_EMBARQUEMENT ,
PLAQUE_REMORQUE,
Parc,
CODE_LIGNE,
activite,
count(CODE_CARRIER) as Nombre_Carrier
resident Fact_DW_Temp
Where CODE_LIGNE = 'MAR'
Group by DATE_DOSSIER,
CODE_SENS,
CODE_COMPAGNIE_MARITIME,
CODE_CARRIER,
CODE_CGV,
DATE_EMBARQUEMENT ,
PLAQUE_REMORQUE,
Parc,
CODE_LIGNE,
activite;
drop table Fact_DW_Temp;
