Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 4 tables:
Two tables are History Tables that contain all the data.
Two tables are Delta Tables-contained partial data from the history tables.
I have the following code and I want to use "WHERE EXISTS/NOT EXISTS":
how can i build the "logNew" table and "LeadsNew" table correctly.
log_history:
LOAD * INLINE [
ID,Leads_Unique,Name,colur
A,1,shanihistory, red
B,2,ifatdelta, green
C,3,bellehistory, blue
D,4,oranhistory,black
];
NoConcatenate
log_delta:
LOAD * INLINE [
ID ,Leads_Unique,Name,colur
A,1,shanidelta, red
B,2,ifatdelta, green
];
NoConcatenate
LeadsHistory:
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
1,a, damrihistory
2,b, solomonov-history
3,c, damrisolomondelta
4,d, solomondelta
];
NoConcatenate
Leadsdelta:
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
3,c, damrisolomondelta
4,d, solomondelta
];
//---------------------------------------------------------------------------------------------------------------------------------------//
logNew:
load * Resident log_delta
where Exists (Leads_Unique); //******from table LeadsHistory
Concatenate
load * Resident log_history
where Exists (Leads_Unique) //******from table Leadsdelta
and not Exists (ID); //******from table log_delta
NoConcatenate
LeadsNew:
load * Resident LeadsHistory
where Exists (Leads_Unique)//******from table log_delta
and not Exists (Leads_Unique); //******from table Leadsdelta
Concatenate
load * Resident Leadsdelta
where Exists (Leads_Unique); //******from table log_history
drop table log_history;
drop table log_delta;
drop table LeadsHistory;
drop table Leadsdelta;
Thanks in advance
QUALIFY *;
log_history:
LOAD * INLINE [
ID,Leads_Unique,Name,colur
A,1,shanihistory, red
B,2,ifatdelta, green
C,3,bellehistory, blue
D,4,oranhistory,black
];
NoConcatenate
log_delta:
LOAD * INLINE [
ID ,Leads_Unique,Name,colur
A,1,shanidelta, red
B,2,ifatdelta, green
];
NoConcatenate
LeadsHistory:
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
1,a, damrihistory
2,b, solomonov-history
3,c, damrisolomondelta
4,d, solomondelta
];
NoConcatenate
Leadsdelta:
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
3,c, damrisolomondelta
4,d, solomondelta
];
UNQUALIFY *;
//---------------------------------------------------------------------------------------------------------------------------------------//
logNew:
load log_delta.ID as ID
,log_delta.Leads_Unique as Leads_Unique
,log_delta.Name as Name
,log_delta.colur as colur
Resident log_delta
where Exists (LeadsHistory.Leads_Unique, log_delta.Leads_Unique); //******from table LeadsHistory
Concatenate
load log_history.ID as ID
,log_history.Leads_Unique as Leads_Unique
,log_history.Name as Name
,log_history.colur as colur
Resident log_history
where Exists (Leadsdelta.Leads_Unique, log_history.Leads_Unique) //******from table Leadsdelta
and not Exists (log_delta.ID, log_history.ID); //******from table log_delta
Try to load this code and check whether or not this is a result to wish to get?
Exists first parameter is used to identify Where you wish to check data and the second one What field you wish to check.
Could you please collaborate and tell us what end result do you expect?
QUALIFY *;
log_history:
LOAD * INLINE [
ID,Leads_Unique,Name,colur
A,1,shanihistory, red
B,2,ifatdelta, green
C,3,bellehistory, blue
D,4,oranhistory,black
];
NoConcatenate
log_delta:
LOAD * INLINE [
ID ,Leads_Unique,Name,colur
A,1,shanidelta, red
B,2,ifatdelta, green
];
NoConcatenate
LeadsHistory:
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
1,a, damrihistory
2,b, solomonov-history
3,c, damrisolomondelta
4,d, solomondelta
];
NoConcatenate
Leadsdelta:
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
3,c, damrisolomondelta
4,d, solomondelta
];
UNQUALIFY *;
//---------------------------------------------------------------------------------------------------------------------------------------//
logNew:
load log_delta.ID as ID
,log_delta.Leads_Unique as Leads_Unique
,log_delta.Name as Name
,log_delta.colur as colur
Resident log_delta
where Exists (LeadsHistory.Leads_Unique, log_delta.Leads_Unique); //******from table LeadsHistory
Concatenate
load log_history.ID as ID
,log_history.Leads_Unique as Leads_Unique
,log_history.Name as Name
,log_history.colur as colur
Resident log_history
where Exists (Leadsdelta.Leads_Unique, log_history.Leads_Unique) //******from table Leadsdelta
and not Exists (log_delta.ID, log_history.ID); //******from table log_delta
Try to load this code and check whether or not this is a result to wish to get?
Exists first parameter is used to identify Where you wish to check data and the second one What field you wish to check.
Try this:
log_history:
LOAD *,
Leads_Unique as Leads_Unique_log_history,
ID as ID_log_history;
LOAD * INLINE [
ID,Leads_Unique,Name,colur
A,1,shanihistory, red
B,2,ifatdelta, green
C,3,bellehistory, blue
D,4,oranhistory,black
];
NoConcatenate
log_delta:
LOAD *,
Leads_Unique as Leads_Unique_log_delta,
ID as ID_log_delta;
LOAD * INLINE [
ID ,Leads_Unique,Name,colur
A,1,shanidelta, red
B,2,ifatdelta, green
];
NoConcatenate
LeadsHistory:
LOAD *,
Leads_Unique as Leads_Unique_LeadsHistory;
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
1,a, damrihistory
2,b, solomonov-history
3,c, damrisolomondelta
4,d, solomondelta
];
NoConcatenate
Leadsdelta:
LOAD *
Leads_Unique as Leads_Unique_Leadsdelta;
LOAD * INLINE
[Leads_Unique,Leads_Status,Lead_Category
3,c, damrisolomondelta
4,d, solomondelta
];
//---------------------------------------------------------------------------------------------------------------------------------------//
logNew:
LOAD *
Resident log_delta
Where Exists (Leads_Unique_LeadsHistory, Leads_Unique); //******from table LeadsHistory
Concatenate
LOAD *
Resident log_history
Where Exists (Leads_Unique_Leadsdelta, Leads_Unique) //******from table Leadsdelta
and not Exists (ID_log_delta, ID); //******from table log_delta
NoConcatenate
LeadsNew:
LOAD *
Resident LeadsHistory
Where Exists (Leads_Unique_log_delta, Leads_Unique)//******from table log_delta
and not Exists (Leads_Unique_Leadsdelta, Leads_Unique); //******from table Leadsdelta
Concatenate
LOAD *
Resident Leadsdelta
Where Exists (Leads_Unique_log_history, Leads_Unique); //******from table log_history
drop table log_history;
drop table log_delta;
drop table LeadsHistory;
drop table Leadsdelta;
Thank you all!!!