Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
Temp:
Month Total
may2018 50
may2018 60
may2018 70
may2018 100
may 2019 45
may 2019 50
may 2019 75
may 2019 200
aug2018 75
aug2018 34
aug2018 20
Output:
Temp:
month total Current
may2018 50 100
may2018 60 100
may2018 70 100
may2018 100 100
may 2019 45 200
may 2019 50 200
may 2019 75 200
may 2019 200 200
aug2018 75 20
aug2018 34 20
aug2018 20 20
Above is the table 'Temp' , I need to create a new column 'Current' in it where it should display the last total value of every month with the respective year.
Can you help me out. Thanks.
Try this?
Table1:
LOAD *, RowNo() AS RowNum INLINE [
Month, TotalVal
may2018, 50
may2018, 60
may2018, 70
may2018, 100
may2019, 45
may2019, 50
may2019, 75
may2019, 200
aug2018, 75
aug2018, 34
aug2018, 20
];
LEFT JOIN(Table1)
LOAD Month,
LastValue(TotalVal) AS Value
Resident Pivot
Group By Month;
Hi
Try this.
Temp:
LOAD *,RowNo() as Rownum INLINE [
Month,Sales
may2018,50
may2018, 60
may2018,70
may2018,100
may 2019,45
may 2019,50
may 2019,75
may 2019, 200
aug2018, 75
aug2018, 34
aug2018, 20];
left JOIN (Temp)
LOAD FirstSortedValue(Sales,-Rownum) as Current, Month,1 as Flag,FirstSortedValue(Rownum,-Rownum) as MaxRow Resident Temp Group by Month;
NoConcatenate
Data:
LOAD * Resident Temp where Flag='1';
DROP TABLE Temp;
@ arjun rao or simply ,
Temp:
LOAD *,RowNo() as Rownum INLINE [
Month,Sales
may2018,50
may2018, 60
may2018,70
may2018,100
may 2019,45
may 2019,50
may 2019,75
may 2019, 200
aug2018, 75
aug2018, 34
aug2018, 20];
NoConcatenate
Data:
LOAD Month, FirstSortedValue(Sales,-Rownum) as Sales
Resident Temp Group by Month;
DROP Table Temp;