Creating a table showing the excluded items in a list

[isciberras]

Hi everyone, 

I need some help, I have a table with teams and their associated assets as shown in the table below (I also attached a QlikView document to better explain what I am trying to achieve)

Team	Assets
A	books
B	books
C	books
A	Radio
B	Radio
C	Radio
D	Radio

So from the table above I can see that Team A,B,C have books however Team D does not. We can identify that a Team D exists because Team D has a radio. 

Does anyone know what syntax I can write to identify that team D does not own the asset 'books'

------

The output of the table I am trying to create would ideally look like:

Im hoping to create a table which looks like:

Assets	Team that does not have Asset
Books	D

Thanks,

Isaac

[Sergey_Shuklin · Accepted solution]

Hello!

Use this script to get table with missed assets linked with your base table by Team field:

Raw_Data:
LOAD [Team],
     Assets,
     1 as Has_asset     
FROM
[Dummy Data.xlsx]
(ooxml, embedded labels);

full_data:
LOAD Distinct Team Resident Raw_Data;

Join(full_data)
LOAD Distinct Assets Resident Raw_Data;

Left Join(full_data)
LOAD * Resident Raw_Data;

missed_assets:
LOAD 
Team, Assets as missed_assets Resident full_data where Has_asset <> 1;

DROP Table full_data;
DROP Field Has_asset;

[isciberras]

I can't thank you enough! it works perfectly. 

I spent weeks trying to figure out how to get it to work, thank you so much for your help!😂

[Sergey_Shuklin]

Hello!

Glad to help 😃