Data model problem

zied_ahmed1 — Thu, 06 Jul 2017 10:25:52 GMT

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;

topic Data model problem in Management & Governance

Data model problem