Concatenate with Where not Exists() is not concatentating records from second table

[#1]

Hi all

I am concatenating the following two tables

tblSurveyMeasure:

LOAD
SYS_ID
,Max(If(IsNull(DATE_COMPLETED), Floor(PLANNED), Floor(DATE_COMPLETED))) AS TechValDate
Resident
tblMeasure
Where
VOID = 'N' and WORK_CLASS = 'BS' and WildMatch(Lower(MEASURE), '*survey*') = 1 and (not IsNull(DATE_COMPLETED) or not IsNull(PLANNED))
Group By
SYS_ID;

tblTechValStatus:

NoConcatenate
LOAD
SYS_ID
,Max(Date(Floor(DATETIME))) AS TechValDate
Resident
tblLogStatus
Where
Match(Lower(SOP_AFTER), 'tech validation') = 1
Group By
SYS_ID;

Concatenate (tblSurveyMeasure)
LOAD
SYS_ID
,TechValDate
Resident
tblTechValStatus
Where
not Exists(SYS_ID);

Having tested the two tables independently I know that there are SYS_IDs present in the second table (tblTechValStatus) that are not present in the first table (tblSurveyMeasure), yet no records from the second table are concatenated.

When I remove the Where not Exists clause then all records from the second table are concatenated, which leads me to believe that it is the Where clause that is causing the problem.

Can anyone give any pointers on how the Exists function works.

Thank you.

[kumarnatarajan]

Hi,

Rename the field name in tblTechValStatus table SYS_ID as SYS_No

See the below script

Ex :

tblTechValStatus:

NoConcatenate
LOAD
SYS_ID AS SYS_No
,Max(Date(Floor(DATETIME))) AS TechValDate
Resident
tblLogStatus
Where
Match(Lower(SOP_AFTER), 'tech validation') = 1
Group By
SYS_ID;

Concatenate (tblSurveyMeasure)
LOAD
SYS_ID AS SYS_No
,TechValDate
Resident
tblTechValStatus
Where
not Exists(SYS_No,SYS_ID);

[Peter_Cammaert]

I think Kumar has got it right. But why is that?

Exists() uses a Field name as first parameter, not a column name from a specific table. In your first example, before you try to concatenate, SYS_ID is a field that contains all distinct values from alle tables where there is a column called SYS_ID (just like a list box on screen). Your test for ...Not Exists(SYS_ID)... will not return any values because they all do exist in either the first or the second table.

Kumar's example restricts the exists() test to the first table only by defining a local column SYS_No.

Best,

Peter

[kumarnatarajan]

Hi Peter,

Yes exists() function consider all previous table referring  SYS_ID value not only concatenate table.

EX: You are loaded all SYS_ID in tblLogStatus and tblTechValStatus.

[Peter_Cammaert]

Sorry, was just clarifying your example to James.

Peter

[#6 · Accepted solution]

Hi Green, Please find the below info from Help file on Exists function:

exists(field [ , expression ] )

Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field. If omitted, the current record’s value in the specified field will be assumed.

In your case, the Exists function load the other than SYS_ID's from tblSurevyMeasure and distinct SYS_ID's from current load statement.

Please try Like below:

tblSurveyMeasure:

NoConcatenate

LOAD
SYS_ID ,

SYS_ID as Temp_Sysid ,
,Max(If(IsNull(DATE_COMPLETED), Floor(PLANNED), Floor(DATE_COMPLETED))) AS TechValDate
Resident
tblMeasure
Where
VOID = 'N' and WORK_CLASS = 'BS' and WildMatch(Lower(MEASURE), '*survey*') and (not IsNull(DATE_COMPLETED) or notIsNull(PLANNED))
Group By
SYS_ID;

Concatenate (tblSurveyMeasure)

LOAD

SYS_ID
,Max(Date(Floor(DATETIME))) AS TechValDate
Resident
tblLogStatus
Where
Match(Lower(SOP_AFTER), 'tech validation')

and not exists(Temp_Sysid,SYS_ID)
Group By
SYS_ID;

Drop Field Temp_Sysid ;

[#7]

Hi Kumar,

I have one clarification generally Exists function need only only parameter value(look-up value is existing table column_name(with same name)) only right?