topic WHERE EXISTS/NOT EXISTS Problem in QlikView

WHERE EXISTS/NOT EXISTS Problem

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: WHERE EXISTS/NOT EXISTS Problem

MindaugasBacius — Mon, 09 Jan 2017 10:13:31 GMT

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

Re: WHERE EXISTS/NOT EXISTS Problem

MindaugasBacius — Mon, 09 Jan 2017 10:25:01 GMT

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.

Re: WHERE EXISTS/NOT EXISTS Problem

sunny_talwar — Mon, 09 Jan 2017 10:36:45 GMT

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;

Re: WHERE EXISTS/NOT EXISTS Problem

Tue, 10 Jan 2017 08:37:03 GMT

Thank you all!!!