# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor

## Subsets in List A not in List B

Hi all. Sorry very new to Qlik.

I've had a quick search.

I have a fact table which lists of products , and a year field for dates they were available for sale.

So Product , Year

How can I get a display of what been added/removed form one  year to the next. Thanks in advance.

4 Replies
Contributor

## Re: Subsets in List A not in List B

Let's follow this example:

```Products:
LOAD * INLINE [
Product, Year
A, 2017
A, 2016
A, 2018
A, 2019
B, 2016
B, 2017
C, 2018
];

Left join(Products)
Load
Product,
StartYear,
if(MaxYear = year(Today()), null(), MaxYear) as LastYear;
Load
Product,
Min(Year) as StartYear,
Max(Year) as MaxYear
Resident Products
group by Product;

LET vStartYear = 2016;
LET vEndYear = Year(Today());
// create years range and join to this list Products added/removed in selected year
Changes:
Load
\$(vStartYear) + IterNo() -1 as Year
AutoGenerate 1 While \$(vStartYear) + IterNo() -1 <= \$(vEndYear);

left join (Changes)
Load distinct
StartYear as Year,
Product as ProductAdded
Resident Products;

left join (Changes)
Load distinct
LastYear+1 as Year,
Product as ProductRemoved
Resident Products;```

New Contributor

## Re: Subsets in List A not in List B

Cheers Jacek, excellent work. I'll give it a go.

Highlighted
New Contributor

## Re: Subsets in List A not in List B

Thanks for the below solution , works perfectly except one thing. For each product it not necessarily recorded all years its actually held. (ive simplified what im actually doing) , so say a product was last listed as 2016. The next list might not have been done till 2018, so I need the date removed not to be 2016 + 1, but 2018?

would it be possible to have a rowno that corresponds to last date and start date in the products table, mainly to find the next year after the year it was last on the product list but which isn't necessarily just say 2016 + 1.

I I can obviously get a rowno into a table containing my distinct years from products, but how would I get that into the products table?

It could be the next time the pructs where all listed was 2018, so that would be the year I want it marked as removed?

Hope that makes sense. Thanks in advance.

Contributor

## Re: Subsets in List A not in List B

Anthony, I cannot understand that fully. May I ask you to prepare example: your source, current selection and expected result?