error in code

[zied_ahmed1]

Hello,

i have something wrong in this code :

left join(tmp)

LOAD

    Pays,

    Langitude,

    Latitude,

    Code_Ligne

FROM ///

(ooxml, embedded labels, table is Feuil1);

tmp_targa:

LOAD COMMESSA as Dossier_tmp

Where WildMatch(COMMESSA, 'AU*')

;

SELECT COMMESSA

FROM ///

[COM_BI_Dossier_Cat]:

LOAD

   autonumber( date(date_dossier, 'DD/MM/YYYY'),'%Date ID')  as [%Date ID]

   ,CodAttivita as Code_Activite,

  ACTIVITE as Designation_Activite,

  sens as Sens,

  FilialeInserimento as Filiale_Creation_Dossier,

  FilOrigine as [Filiale Origine],

  PaysOrigine as [Pays Fililale Origine],

  FilialeDestino as [Filiale Destination],

  Code_Ligne as Code_Ligne,

  Dossier ,

  ViaggioNave as Num_Voyage,

  date(date_dossier,'DD/MM/YYYY') as Date_Dossier,

  DateEmbarquement as Date_Embarquement,

  DateDebarquement as Date_Debarquement,

  VideCpltGrpg as Groupe_Complet_Vide,

  Plaque,

     Parc,

  TypeMateriel as Type_Materiel,

      DesCompagnia as Compagnie,

  Remorque as Remorque_Parc_HorsParc,

   // if(Code_Ligne='MAR' and Date_Embarquement )

  ViaggioLinea as Voyage_Ligne,

   // ViaggioNave as Voyage_bateau,

  carrier as Code_Carrier

    ,Pays,

    geomakepoint(Latitude,Langitude) as PAYS2

resident tmp  Where NOT Exists(Dossier_tmp,Dossier);

drop table tmp;

final:

load*,'Vide' as Charge_Vide;

load * Resident [COM_BI_Dossier_Cat] where (upper(Groupe_Complet_Vide)='V');

load*,'Chargé' as Charge_Vide;

load * Resident [COM_BI_Dossier_Cat] where (upper(Groupe_Complet_Vide)<>'V');

drop table [COM_BI_Dossier_Cat];

NoConcatenate

Fact_Final:

Load  Date_Embarquement,   

    Plaque,

    if( Nombre_Carrier>1, 1, 0) as Flag_Doublon;

LOAD  

    Date_Embarquement,   

    Plaque,  

  count(Code_Carrier) as Nombre_Carrier

Resident final

Where Code_Ligne = 'MAR' 

Group by Date_Embarquement,Plaque

;

join(Fact_Final)

LOAD  *

Resident final

Where Code_Ligne = 'MAR'  ;

when i drop table final i have just informations of the Fact_Final,when i drop final i have just data of Fact Final what i should do ?

Thanks

[zied_ahmed1]

stalwar1‌

[sunny_talwar]

May be try this

.

.

.

[COM_BI_Dossier_Cat]:

LOAD

  autonumber( date(date_dossier, 'DD/MM/YYYY'),'%Date ID')  as [%Date ID]

  ,CodAttivita as Code_Activite,

  ACTIVITE as Designation_Activite,

  sens as Sens,

  FilialeInserimento as Filiale_Creation_Dossier,

  FilOrigine as [Filiale Origine],

  PaysOrigine as [Pays Fililale Origine],

  FilialeDestino as [Filiale Destination],

  Code_Ligne as Code_Ligne,

  Dossier ,

  ViaggioNave as Num_Voyage,

  date(date_dossier,'DD/MM/YYYY') as Date_Dossier,

  DateEmbarquement as Date_Embarquement,

  DateDebarquement as Date_Debarquement,

  VideCpltGrpg as Groupe_Complet_Vide,

  Plaque,

    Parc,

  TypeMateriel as Type_Materiel,

      DesCompagnia as Compagnie,

  Remorque as Remorque_Parc_HorsParc,

  // if(Code_Ligne='MAR' and Date_Embarquement )

  ViaggioLinea as Voyage_Ligne,

  // ViaggioNave as Voyage_bateau,

  carrier as Code_Carrier

    ,Pays,

    geomakepoint(Latitude,Langitude) as PAYS2

resident tmp  Where NOT Exists(Dossier_tmp,Dossier);

drop table tmp;

Fact_Final:

LOAD *,

    'Vide' as Charge_Vide

Resident [COM_BI_Dossier_Cat]

Where Upper(Groupe_Complet_Vide) = 'V';

Concatenate (Fact_Final)

LOAD *,

    'Chargé' as Charge_Vide

Resident [COM_BI_Dossier_Cat]

Where Upper(Groupe_Complet_Vide) <> 'V';

DROP Table [COM_BI_Dossier_Cat];

Left Join (Fact_Final)

Load  Date_Embarquement, 

    Plaque,

    If(Nombre_Carrier > 1, 1, 0) as Flag_Doublon;

LOAD Date_Embarquement,

    Plaque,

    Count(Code_Carrier) as Nombre_Carrier

Resident Fact_Final

Where Code_Ligne = 'MAR'

Group by Date_Embarquement, Plaque;

See if the above script works.....

[zied_ahmed1]

Thanks i find a solution

[sunny_talwar]

What was the solution? Can you share that with us?

Thanks,

Sunny

[zied_ahmed1]

actually this is the problem of the other problem posted,so it's not resolved and i tryed you code but it doesn't work what i can do

[OmarBenSalem]

Actually Sunny, we're trying to solve the same problem (I shared with friday) in a different way:

This is how we altered the script:

Fact_Final:

Load  Date_Embarquement,  

    Plaque,

    if( Nombre_Carrier>1, 1, 0) as Flag_Doublon;

LOAD 

    Date_Embarquement,  

    Plaque, 

  count(Code_Carrier) as Nombre_Carrier

Resident final

Where Code_Ligne = 'MAR'

Group by Date_Embarquement,Plaque

;

join(Fact_Final)

LOAD  *

Resident final

Where Code_Ligne = 'MAR'  ;

Concatenate

load * resident final where Code_Carrier <>'MAR';

drop table final;

Now, in the presentation, we created a table like this :

As dimension; code_ligne , as column month of dates

and the expression is :

if (Code_Ligne='MAR',if (

Mod(count( distinct {<[Year of date]={$(=max([Year of date]))}, [Month of date]=,Timeframe='Annuel',Code_Ligne={'MAR'},Groupe_Complet_Vide={C,G},Flag_Doublon={1}>} Plaque),2)=1,

(

Count({<[Year of date]={$(=max([Year of date]))},Code_Ligne={'MAR'}, [Month of date]=,Timeframe='Annuel',Groupe_Complet_Vide={C,G},Flag_Doublon={1}>}Dossier)

/

count(  {<[Year of date]={$(=max([Year of date]))},Code_Ligne={'MAR'}, [Month of date]=,Timeframe='Annuel',Groupe_Complet_Vide={C,G},Flag_Doublon={1}>} Plaque)

)

+

Count({<[Year of date]={$(=max([Year of date]))},Code_Ligne={'MAR'},[Month of date]=,Timeframe='Annuel',Groupe_Complet_Vide={C,G},Flag_Doublon={0}>}Dossier),

(

Count({<[Year of date]={$(=max([Year of date]))},[Month of date]=,Code_Ligne={'MAR'},Timeframe='Annuel',Groupe_Complet_Vide={C,G},Flag_Doublon={1}>}Dossier)

/

count( distinct {<[Year of date]={$(=max([Year of date]))},[Month of date]=,Code_Ligne={'MAR'},Timeframe='Annuel',Groupe_Complet_Vide={C,G},Flag_Doublon={1}>} Plaque)

)

+

Count({<[Year of date]={$(=max([Year of date]))},[Month of date]=,Timeframe='Annuel',Code_Ligne={'MAR'},Groupe_Complet_Vide={C,G},Flag_Doublon={0}>}Dossier)

),

Count({<[Year of date]={$(=max([Year of date]))}, [Month of date]=,Groupe_Complet_Vide={C,G}>}Dossier))

Now, the table is as follow and all the values are correct :

Now, if I choose a month, the values mustn't change at all : BUT when  I do, only the values of Ligne Maroc change (wrong values), only the month selected returns a correct value(iin this case, I selected Month : Mai)

This is what I can't understand.

So stalwar1‌, if you can understand what could be causing this, your help would be much appreciated, as always !

[zied_ahmed1]

Hello stalwar1‌ do you have any solution for this ?

[sunny_talwar]

Would you be able to email me the application? I can take a look once I am home

[zied_ahmed1 · Accepted solution]

Thank you Sunny for your help the solution was

if (Only({<[Month of date]>}Pays)='MAR',