Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
isciberras
Creator
Creator

Creating a table showing the excluded items in a list

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)

TeamAssets
Abooks 
Bbooks 
Cbooks
ARadio
BRadio
CRadio
DRadio

 

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:

AssetsTeam that does not have Asset
BooksD
  
  
  
  
  
  
  
  

 

Thanks,

Isaac

Labels (2)
1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

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;

 

View solution in original post

3 Replies
Sergey_Shuklin
Specialist
Specialist

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
Creator
Creator
Author

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
Specialist
Specialist

Hello!

Glad to help 😃