Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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)';
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
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;
Great. It works