Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
I have a case where I need to create a column the maximum value that is in another column.
The data looks like this:
Date | Data |
---|---|
01.01.2015 | 100 |
01.01.2015 | 200 |
150 | |
01.01.2016 | 100 |
120 | |
01.01.2018 | 130 |
140 | |
01.01.2015 | 170 |
The goal is to get a column with the max date in every cell:
Date | Data |
---|---|
01.01.2018 | 100 |
01.01.2018 | 200 |
01.01.2018 | 150 |
01.01.2018 | 100 |
01.01.2018 | 120 |
01.01.2018 | 130 |
01.01.2018 | 140 |
01.01.2018 | 170 |
I have a big data set and multiple sheets, so I need to make the operation nice and easy.
Thank you guys!
temp:
Load
max(Date) as MaxDate
From <>;
Let vMaxDate=Date(Peek('MaxDate'));
Drop table temp;
final:
Load
'$(vMaxDate)' as Date,
Data
From <>;
Hi
try some thing like this
Table1:
LOAD Date(Date#(Date,'DD.MM.YYYY')) as Date, Data INLINE [
Date, Data
01.01.2015, 100
01.01.2015, 200
,150
01.01.2016, 100
,120
01.01.2018, 130
,140
01.01.2015, 170
];
Join(Table1)
LOAD Date(max(Date)) as MaxDate Resident Table1;
Edit:
Use, MaxDate as Dimension.
Please see the attached one
Please find the attached QVW file.I have done it at script level.