Comparing two tables and creating a new with the non matching output

[#1]

Hi,

I have two tables with the following fields and data:

App Name Total
App1
App2
App3
App4

App Name Active
App1
App2
App3

Wanted output:

[Inactive apps]:

Unused
App4

I have tried loading the first table in a temp table, then joining the second field with this table, and then doing a resident load in a final table using the following code:

temp:

load

     AppName as [App Name Active]

Resident [(...another table)];

Concatenate

load

Document as [App Name Total]

resident [(...another table)];

[Inactive apps]:

NoConcatenate

LOAD

     [App Name Total],

     [App Name Active]

Resident temp

Where [App Name Total] <> [App Name Active];

DROP Table temp;

Any ideas on why this is not giving me the correct output?

Best regards,

[Colin-Albert]

Try something like this

Active:

load * inline [
Apps
App1
App2
App4
] ;

AllApps:

load * inline [
AllApps
App1
App2
App3
App4
] ;

ActiveApps:

load AllApps as Inactive

resident AllApps

where not exists(Apps, AllApps) ;

[MK_QSL]

T1:

Load * Inline

[

  NAME2

  App1

  App2

  App3

];

NoConcatenate

T2:

Load * Inline

[

  NAME1

  App1

  App2

  App3

  App4

];

T3:

Load NAME1 as NAME3 Resident T2 Where Not Exists (NAME2,NAME1);

[#4]

Does an inline load have to be used? My sources are a excel file and a .txt file.

[MK_QSL · Accepted solution]

No Inline Load is not compulsory.

You can use something like

Load NAME2 FROM TABLENAME2;

NoConcatenate    //Or you can avoid NoConcatenate  also

Load NAME1 FROM TABLENAME1;

T3;

Load NAME1 as NAME3 Resident T2 Where Not Exists (NAME2,NAME1);

[Colin-Albert]

No , the load can be from any source. I simply used an inline load to create a simple example that others can use and test without any other data.