Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Cheers Jacek, excellent work. I'll give it a go.
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.