Trouble with the max() fct

[datanibbler]

Hi,

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!

Best regards,

DataNibbler

[simondachstr]

As you concatenate it, maybe it's being interpreted as a string? -> num().

[Gysbert_Wassenaar]

Can you post a sample document that demonstrates the problem?

[#4]

Dear DataNibbler

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.

Regards

RL

[datanibbler]

Hi all,

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.

[datanibbler]

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!

Best regards,

DataNibbler

[datanibbler]

Hi,

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!

Best regards,

DataNibbler

[#8]

if i was you i would left join into Max_trouble_3:

Max_trouble_3:

LOAD

     item_number,

     Deliv_day_and_shift,

     overall_quantity

RESIDENT Max_trouble_3;

Left Join

LOAD

    item_number,

    max(Deliv_day_and_shift) as Deliv_day_and_shift

RESIDENT Max_trouble_3

GROUP BY item_number, overall_quantity

;

Regards

Rahul

[datanibbler]

Hi Rahul,

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.

[datanibbler]

Hi,

I will just post here the code I am using. Maybe you can spot an error in there:

TRANS_DETAIL_pre:
LOAD
     %ITEM_NUMBER,
     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,
     INVT_TRANS_DETAIL.ITEM_NUMBER,
// shorten the item_nr
     SubField(INVT_TRANS_DETAIL.ITEM_NUMBER, '-', 1) as INVT_TRANS_DETAIL.ITEM_Nr_special,
//     INVT_TRANS_DETAIL.TRAN_DATE,
// filter the TRAN_CODE
     INVT_TRANS_DETAIL.TRAN_CODE,
     INVT_TRANS_DETAIL.QUANTITY,
// calculate the shift
    IF(TIME(FRAC(INVT_TRANS_DETAIL.TRAN_DATE), 'hh:mm:ss') < '14:00:00', 1, 2) as INVT_TRANS_DETAIL.SHIFT
FROM
..\01_QVD\QVD\INVT_TRANS_DETAIL.qvd (qvd)
WHERE (INVT_TRANS_DETAIL.TRAN_CODE = 'SPRECEIVE')
;

NoConcatenate

Trans_Detail:
LOAD
     %ITEM_NUMBER,
     INVT_TRANS_DETAIL.ITEM_NUMBER,
     INVT_TRANS_DETAIL.TRAN_DATE_Date,
     INVT_TRANS_DETAIL.TRAN_DATE_Time,
     INVT_TRANS_DETAIL.SHIFT,
     num((num(INVT_TRANS_DETAIL.TRAN_DATE_Date) & INVT_TRANS_DETAIL.SHIFT)) as Deliv_day_and_shift,
     INVT_TRANS_DETAIL.NAEL_AI_TD, 
//     INVT_TRANS_DETAIL.TRAN_DATE,
     INVT_TRANS_DETAIL.TRAN_CODE,
     INVT_TRANS_DETAIL.QUANTITY,
     INVT_TRANS_DETAIL.ITEM_Nr_special
RESIDENT TRANS_DETAIL_pre
;

DROP TABLE TRANS_DETAIL_pre;

// aggregate on the combination of date and shift

NoConcatenate

Trans_Detail_v2:
LOAD
     %ITEM_NUMBER,
//     INVT_TRANS_DETAIL.ITEM_NUMBER,
     INVT_TRANS_DETAIL.ITEM_Nr_special,
     Deliv_day_and_shift,
     INVT_TRANS_DETAIL.NAEL_AI_TD,
     INVT_TRANS_DETAIL.TRAN_CODE,
     sum(INVT_TRANS_DETAIL.QUANTITY) as Delivery_qty
RESIDENT Trans_Detail
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

NoConcatenate
Trans_Detail_joiner:
LOAD
    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
RESIDENT Trans_Detail_v2;
//

NoConcatenate

Trans_Detail_v3:
LOAD
     %ITEM_NUMBER,
     INVT_TRANS_DETAIL.ITEM_Nr_special,
     rowno() as line_v3,
     max(Deliv_day_and_shift) as last_delivery
RESIDENT Trans_Detail_v2
GROUP BY %ITEM_NUMBER, INVT_TRANS_DETAIL.ITEM_Nr_special, rowno()
;

DROP TABLE Trans_Detail_v2;

EXIT Script;

// last, join the NAEL_AI to that table

LEFT JOIN (Trans_Detail_v3)
LOAD
    Item_Nr as INVT_TRANS_DETAIL.ITEM_Nr_special,
    max(dayshift) as last_delivery,
    new_NAEL_AI as neuer_NAEL_AI
RESIDENT Trans_Detail_joiner
GROUP BY INVT_TRANS_DETAIL.ITEM_Nr_special, INVT_TRANS_DETAIL.NAEL_AI_TD
;

DROP TABLE Trans_Detail_joiner;
    
EXIT Script;