Take the latest data for a particular month

[safik003]

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.

[gavinlaird]

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)';

[safik003]

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

[Vegar · Accepted solution]

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;

[safik003]

Great. It works