Where Clause2

[#1]

The syntax for the following where clause is correct, the script runs without error, but the expected result is incorrect as if it ignored the where conditions.  I'm expecting to see only records where Entity Status2='A', Studflag='STUD' AND Profession = 'NUR' AND Yrsins2=1.  The output includes all records.  I've attached the qvw document, but the data is too large to attach.

T1_ActiveTerm3:
LOAD *
Resident T1_ActiveTerm2
WHERE ([Entity Status2] = 'A' AND Studflag='STUD' AND Profession = 'NUR' AND Yrsins2=1);

[#2]

I just revised the script as follows and the where clause works.  I'm not sure why two LOADS are required to make the WHERE clause work.

T1_ActiveTerm3:
LOAD *
WHERE ([Entity Status2] = 'A' AND Studflag='STUD' AND Profession = 'NUR' AND Yrsins2=1);
LOAD [Account No],[Entity Status2],Studflag,Profession,Yrsins2
Resident T1_ActiveTerm2;
Drop Table T1_ActiveTerm2

[Gysbert_Wassenaar · Accepted solution]

The records from that load statement will be appended to the table T1_ActiveTerm2 because you load exactly the same fields. That's why you see all the records.

Add the NOCONCATENATE keyword before the LOAD. And drop the table T1_ActiveTerm2 afterwards or you'll get a massive synthetic key which will likely cripple performance.

[#4]

I had already done a slight modification to your answer (see my reply).  Do you know why I need two LOAD statements ?

[Gysbert_Wassenaar]

You don't need two. But your modification specifically list fields. That list of fields is a subset of all the fields that exists in T1_ActiveTerm2. Since that will create a table with a different structure (less fields than the source table) the records will end up in the new table instead of being appended to the source table.

[#6]

Thanks !!