Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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.

1 Solution

Accepted Solutions
Not applicable

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

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 ;

6 Replies
kumarnatarajan
Not applicable

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

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
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Sorry, was just clarifying your example to James.

Peter

Not applicable

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

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 ;

Not applicable

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

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?