topic Re: Comparing two tables and creating a new with the non matching output in QlikView

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

Mon, 08 Sep 2014 15:23:05 GMT

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,

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

Colin-Albert — Mon, 08 Sep 2014 15:30:26 GMT

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

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

MK_QSL — Mon, 08 Sep 2014 15:37:40 GMT

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

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

Mon, 08 Sep 2014 15:40:15 GMT

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

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

MK_QSL — Mon, 08 Sep 2014 15:42:42 GMT

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

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

Colin-Albert — Mon, 08 Sep 2014 15:43:24 GMT

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.