topic Re: Take the latest data for a particular month in QlikView

Take the latest data for a particular month

safik003 — Sat, 16 Nov 2024 01:38:50 GMT

I have the below table.

TableA:
LOAD * INLINE[
Category, Brand, Quantity, Month, Year, YearMonth
Electronics, LG, 20, 8, 2018, 2018-08
Electronics, LG, 30, 12, 2018, 2018-12
Electronics, LG, 40, 4, 2019, 2019-04
Electronics, LG, 50, 12, 2019, 2019-12

DRINKS, COKE, 20, 12, 2017, 2017-12
DRINKS, COKE, 20, 12, 2018, 2018-12
FRUITS, APPLE, 25, 12, 2018, 2018-12];

I want the data for Month =12 and latest year for each Category and Brand like below in a different table.

Brand	Category	Month	Quantity	Year	YearMonth
LG	Electronics	12	50	2019	2019-12
COKE	DRINKS	12	20	2018	2018-12
APPLE	FRUITS	12	25	2018	2018-12

but I'm getting data for both the year 2018 and 2019 like below.

Brand	Category	Month	Quantity	Year	YearMonth
LG	Electronics	12	30	2018	2018-12
LG	Electronics	12	50	2019	2019-12
COKE	DRINKS	12	20	2017	2017-12
COKE	DRINKS	12	20	2018	2018-12
APPLE	FRUITS	12	25	2018	2018-12

Below is the script:
TableB:
load Category,

Brand,

Quantity,

Month,

Year+1 as Year,

YearMonth

resident TableA

where Month=12;

Please suggest. test file is attached.

Re: Take the latest data for a particular month

gavinlaird — Fri, 20 Dec 2019 23:46:19 GMT

First, I would store the latest year in a variable:

Temp_maxYear: LOAD max(Year) as maxYear Resident TableA; Let vMaxYear = Peek('maxYear'); Drop Table Temp_maxYear;

Then, I would add another Where clause to the TableB load:

TableB: load Category, Brand, Quantity, Month, Year+1 as Year, YearMonth resident TableA where Month=12 and Year = '$(vMaxYear)';

Re: Take the latest data for a particular month

safik003 — Sat, 21 Dec 2019 04:02:43 GMT

Thanks for your response. It's perfectly working when there is one Category and one Brand with max year as 2019. But problem is arising when there is multiple Category and Brand with different max year for each Category and Brand. I have modified my question accordingly. Please check and suggest

Re: Take the latest data for a particular month

Vegar — Sat, 21 Dec 2019 07:03:05 GMT

I'm sorry that I've haven't got a qv desktop available to examine your qvw and to post a new one, but I think my example below should work.

TableA:
LOAD * INLINE [
Category, Brand, Quantity, Month, Year, YearMonth
Electronics, LG, 20, 8, 2018, 2018-08
Electronics, LG, 30, 12, 2018, 2018-12
Electronics, LG, 40, 4, 2019, 2019-04
Electronics, LG, 50, 12, 2019, 2019-12

DRINKS, COKE, 20, 12, 2017, 2017-12

DRINKS, COKE, 20, 12, 2018, 2018-12
FRUITS, APPLE, 25, 12, 2018, 2018-12];

Inner join (TableA)

Load Category,

Brand,

Month,

max(Year) as Year

Resident TableA

Where Month=12

Group by Category, Brand, Month;

Re: Take the latest data for a particular month

safik003 — Sat, 21 Dec 2019 08:58:40 GMT

Great. It works