Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
starting point is a table with multiple columns, where two columns are relevant to my problem. This is an item ID and a date. I want to get displayed in my table only the items which have the smallest date per item. If there are the same dates for a item then show a random datarow or the row with the smallest PrimaryKey(PK).
Anyone a solution for that?
For example:
Rawdata:
Item ID, Date, PK, colx, coly
1, 12.12.2012, 1, xxx, yyy
1, 12.12.2012, 2, xx2, yy2
1, 18.12.2012, 3, xyz, yxz
2, 02.01.2013, 4, aaa, bbb
3, 08.01.2013, 5, ccc, ddd
3, 09.01.2013, 6, abc, ddd
4, 04.04.2013, 7, ddd, ttt
ResultTable:
Item ID, Date, PK, colx, coly
1, 12.12.2012, 1, xxx, yyy (or 12.12.2012, 2, xx2, yy2)
2, 02.01.2013, 4, aaa, bbb
3, 08.01.2013, 5, ccc, ddd
4, 04.04.2013, 7, ddd, ttt
Thank You!
Use straighttable
1) Use ID as dimension
2) As expressions use
a) Min(Date)
b) FirstSortedValue(PK,Date+PK/1000)
c) FirstSortedValue(colx,Date+PK/1000)
d) FirstSortedValue(coly,Date+PK/1000)
Doesnt work..
Im only getting dimension values in one row, the others are null.
PFA
Thank you!
Weird,
it worked here, step by step