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

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

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,

1 Solution

Accepted Solutions
MK_QSL
Not applicable

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

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


5 Replies
colin_albert
Not applicable

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

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

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

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

Not applicable

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

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

MK_QSL
Not applicable

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

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

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

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.