WHERE EXISTS/NOT EXISTS Problem

[#1]

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

[MindaugasBacius]

Could you please collaborate and tell us what end result do you expect?

[MindaugasBacius · Accepted solution]

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.

[sunny_talwar]

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;

[#5]

Thank you all!!!