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: 
Anthony74
Contributor
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
Jacek
Educator-Ambassador
Educator-Ambassador

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;

 

2019-01-24_14h37_52.png2019-01-24_14h43_04.png

Anthony74
Contributor
Contributor
Author

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

Anthony74
Contributor
Contributor
Author

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.

Jacek
Educator-Ambassador
Educator-Ambassador

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