Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I'm trying to load "min values" from a table. I have redundant rows but I want only the row's which have the min Amount.
Example:
ID, Amount
1, 5
1, 2
2, 3
2, 8
3, 9
In this Example I want only back...
1,2
2,3
3,9
But I don't know to handle this.
Thank you
Data:
LOAD
ID
DateOne
SETDate
DateTwo,
floor(DateTwo)- floor(DateOne) as Diff
FROM table;
New:
LOAD ID,
DateOne,
FirstSortedValue(DateTwo,Diff) as DateTwo
Resident Data
group by ID,DateOne;
drop table Data;
Make sure that your Date fields are in proper format
Thank you for that. There is only one thing missing. I need the SETDate in the New/Final Table...
Add the setdate in load and add it in group by also
Oh sorry. I mean DateTwo
date2 is already there.. see firstsortedvalue
Hi Kushal,
Sorry for the late reply. I started to home so couldn't reply. I have misunderstood the question. Thanks for pointing my mistake.
Hi Hans,
Try this,
Data:
LOAD ID,
DateOne,
SETDate,
DateTwo,
DateOne-DateTwo as Diff
FROM
(biff, embedded labels, table is Sheet1$);
Temp:
Inner Keep
Load ID,
DateOne,
Max(Diff) as Diff
Resident Data
Group by ID, DateOne;
DROP Table Temp;
Now I see it's my fault. In the table there is a column missing -> Amount. I need Amount in my final table
Try this,
Data:
LOAD ID,
DateOne,
SETDate,
DateTwo,
DateOne-DateTwo as Diff,
Amount
FROM
(biff, embedded labels, table is Sheet1$);
Temp:
Inner Keep
Load ID,
DateOne,
Max(Diff) as Diff
Resident Data
Group by ID, DateOne;
DROP Table Temp;
Do below change in my above post
Add Amount field in Data table
Then add one more field amount in New table as below
firstsortedvalue(Amount,Diff) as Amount