Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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;
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.
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?
What is the exact error you got?
Field commessa not found
Does this table have field commessa in it?
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:
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?
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?