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

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

Tue, 28 Jan 2014 12:26:54 GMT

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.

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

kumarnatarajan — Tue, 28 Jan 2014 12:38:31 GMT

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);

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

Peter_Cammaert — Tue, 28 Jan 2014 12:45:38 GMT

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

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

kumarnatarajan — Tue, 28 Jan 2014 12:52:15 GMT

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.

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

Peter_Cammaert — Tue, 28 Jan 2014 12:54:53 GMT

Sorry, was just clarifying your example to James.

Peter

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

Tue, 28 Jan 2014 14:05:08 GMT

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 ;

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

Tue, 28 Jan 2014 14:40:56 GMT

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?