Help with script

[OmarBenSalem]

Hi all, ( stalwar1‌),

I have 2 tables:

table1:

load * Inline [

dossier

AU1

AU2

TN

];

NoConcatenate

table2:

load * Inline [

dossier

AU1

TN

];

What I want to do is:

I want to compare fields in both tables, if I find a 'dossier' in table2 that begins with AU (AU1) I have to drop this field from table1.

So the final table would be only table1 (drop table2 once finished)

And table1 should contain : AU2 (not found in table2) and TN.

A help would be appreciated ; thanks,

[sunny_talwar · Accepted solution]

May be this

table2:

load * Inline [

dossier

AU1

TN

] Where WildMatch(dossier, 'AU*');

table1:

NoConcatenate

load * Inline [

dossier

AU1

AU2

TN

] Where not Exists(dossier);

DROP Table table2;

[Anonymous]

Hey Omar,

I think this document explains the possibilities that you're looking for.

Understanding Join, Keep and Concatenate

Hope this is usefull for you.

[OmarBenSalem]

stalwar1‌,

I tried this  (per analogy), but I keep getting the same error 'commessa field not found':

//table2

tmp_targa:

LOAD commessa ,

atd,

targatrailer,

Grp,

NumVoyage,

ATA,

fact,

modifiePar, 

CreePar,

EmailCreation

   Where WildMatch(commessa, 'AU*');

SELECT commessa,

atd,

targatrailer,

Grp,

NumVoyage,

ATA,

fact,

modifiePar,

CreePar,

EmailCreation

FROM SOFTNETREPORT.dbo."CG_ATD_TARGATRAILER";

// table 1

final:

load *  Where not Exists(commessa);

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 tmp_targa;

drop table [COM_BI_Dossier_Cat];

Why? what should I do?

[sunny_talwar]

What is the exact error you got?

[OmarBenSalem]

Field commessa not found

[sunny_talwar]

Does this table have field commessa in it?

[OmarBenSalem]

Here is the whole code:

tmp:

load *;

SELECT*

FROM ...

where (cond1);

load * ;

SELECT *

FROM ...

where (cond2);

left join(tmp)

LOAD

    Pays,

    Longitude,

    Latitude,

    Code_Ligne

FROM***;

[COM_BI_Dossier_Cat]:

LOAD

bla bla,

Dossier as Num_Dossier

     resident tmp ;

drop table tmp;

//table2

tmp_targa:

LOAD commessa

Where WildMatch(commessa, 'AU*')

;

SELECT commessa

FROM SOFTNETREPORT.dbo."CG_ATD_TARGATRAILER" ;

// table 1

final:

load * Where not Exists(commessa);

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 tmp_targa;

drop table [COM_BI_Dossier_Cat];

error:

[sunny_talwar]

Since you are doing a LOAD * for tmp, there is no way for me to know if commessa is available in tmp and [COM_BI_Dossier_Cat]. Can you check the two queries for tmp to make sure that they have commessa field?

[OmarBenSalem]

If there no commessa field in tmp and [COM_BI_Dossier_Cat], and instead there is 'Dossier' ;

the code should become:

load * Where not Exists(Dossier);

??

If so, how is it comparing Dossier with the commessa existing in the table2?