I have a table with a date and a time.
=> Out of those, I have made a numeric value (date) and a number 1 or 2, depending on which shift the time falls into.
=> I have concatenated those, so I have one number now representing both the date and the shift.
=> Now I want to draw the latest (biggest) value from the table, using the max() function
<=> Somehow that does not work. I get several values, so in a chart I cannot display it - well, I could, but having anything else
than EXACTLY ONE date_time there is no good, and I don't know why I get these anyway? The max() function should return
only one line, no?
(I have aggregated the table on the day_and_shift beforehand, so there should be only one line with every one of those numbers.)
Can anybody help me out with this?
Thanks a lot!
How are you? Sounds like you have a time stamp for date? If that be the case you need to drive another field that is just date and use that for Aggregation.
Please see file attached, it has a scripted as well as a front end solution.
while assembling a fake sample, I have just spotted one more hole I haven't yet poked into ;-)
There is a potential for error in that GROUP BY clause. Let's see. I'll just try that and come back.
That seems to have been the root_cause of my troubles.
It was the GROUP BY clause. i had one too many fields in there - thus in the LOAD - which had the effect that I didn't get the overall last delivery, but the last delivery with that specific value - so in the rather rare cases where that value had ever changed, I got two or more last deliveries.
Thank you all!
there remains one more hurdle to take:
I have actually aggregated my table so that I should have only one record per item_nr and day-shift-combination.
I can now load RESIDENT the overall last delivery of that item_number, but there is now an important value missing, so I have to join it to the table lateron.
I tried to join that (again RESIDENT) from the last table - the one from which I got the last delivery, using the item_nr and the date-shift-combination as join_fields.
For some reason, though, it didn't work.
I will attach the small sample file I have, the code in there is copied from my "real" one.
Thanks a lot!
if i was you i would left join into Max_trouble_3:
max(Deliv_day_and_shift) as Deliv_day_and_shift
GROUP BY item_number, overall_quantity
thanks for the quick answer!
Unfortunately, that doesn't yet solve my problem:
(I had in the meantime tried loading in a RESIDENT only those fields I needed for the join lateron and renaming them, so I didn't have to DROP the original table.
Then I did my regular RESIDENT LOAD and aggregated using max() to the last delivery => worked fine, every item_nr had only one last_delivery.
Then I tried to join (from my special join_table), using item_nr and the day-shift-combination as join_fields
=> that somehow multiplied the day-shift-combinations so I could not display a specific quantity.
I will just post here the code I am using. Maybe you can spot an error in there:
INVT_TRANS_DETAIL.TRAN_DATE_TD as INVT_TRANS_DETAIL.TRAN_DATE_Date,
// calculate the time
TIME(FRAC(INVT_TRANS_DETAIL.TRAN_DATE), 'hh:mm:ss') as INVT_TRANS_DETAIL.TRAN_DATE_Time,
INVT_TRANS_DETAIL.NEAL_AI_TD as INVT_TRANS_DETAIL.NAEL_AI_TD,
// shorten the item_nr
SubField(INVT_TRANS_DETAIL.ITEM_NUMBER, '-', 1) as INVT_TRANS_DETAIL.ITEM_Nr_special,
// filter the TRAN_CODE
// calculate the shift
IF(TIME(FRAC(INVT_TRANS_DETAIL.TRAN_DATE), 'hh:mm:ss') < '14:00:00', 1, 2) as INVT_TRANS_DETAIL.SHIFT
WHERE (INVT_TRANS_DETAIL.TRAN_CODE = 'SPRECEIVE')
num((num(INVT_TRANS_DETAIL.TRAN_DATE_Date) & INVT_TRANS_DETAIL.SHIFT)) as Deliv_day_and_shift,
DROP TABLE TRANS_DETAIL_pre;
// aggregate on the combination of date and shift
sum(INVT_TRANS_DETAIL.QUANTITY) as Delivery_qty
GROUP BY %ITEM_NUMBER, INVT_TRANS_DETAIL.ITEM_Nr_special, Deliv_day_and_shift, INVT_TRANS_DETAIL.NAEL_AI_TD, INVT_TRANS_DETAIL.TRAN_CODE;
DROP TABLE Trans_Detail;
// prepare a table we will need lateron for joining;
// rename fields to avoid synthetic keys
INVT_TRANS_DETAIL.ITEM_Nr_special as Item_Nr,
Deliv_day_and_shift as dayshift,
Delivery_qty as deliv_qty,
INVT_TRANS_DETAIL.NAEL_AI_TD as new_NAEL_AI
rowno() as line_v3,
max(Deliv_day_and_shift) as last_delivery
GROUP BY %ITEM_NUMBER, INVT_TRANS_DETAIL.ITEM_Nr_special, rowno()
DROP TABLE Trans_Detail_v2;
// last, join the NAEL_AI to that table
LEFT JOIN (Trans_Detail_v3)
Item_Nr as INVT_TRANS_DETAIL.ITEM_Nr_special,
max(dayshift) as last_delivery,
new_NAEL_AI as neuer_NAEL_AI
GROUP BY INVT_TRANS_DETAIL.ITEM_Nr_special, INVT_TRANS_DETAIL.NAEL_AI_TD
DROP TABLE Trans_Detail_joiner;