Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Anthony74
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
Jacek
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;

 

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

Anthony74
New Contributor

Re: Subsets in List A not in List B

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

Anthony74
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.

Jacek
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?