topic Re: Creating a table showing the excluded items in a list in QlikView

Creating a table showing the excluded items in a list

isciberras — Sat, 16 Nov 2024 20:42:55 GMT

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

Re: Creating a table showing the excluded items in a list

Sergey_Shuklin — Fri, 07 Jun 2019 16:59:38 GMT

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;

Re: Creating a table showing the excluded items in a list

isciberras — Thu, 13 Jun 2019 10:08:43 GMT

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!😂

Re: Creating a table showing the excluded items in a list

Sergey_Shuklin — Thu, 13 Jun 2019 12:03:27 GMT

Hello!

Glad to help 😃