11 Replies Latest reply: Jun 6, 2014 10:02 AM by Friedrich Hofmann RSS

    Trouble with the max() fct

    Friedrich Hofmann

      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

        • Re: Trouble with the max() fct
          Martin Mahler

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

          • Re: Trouble with the max() fct
            Gysbert Wassenaar

            Can you post a sample document that demonstrates the problem?

            • Re: Trouble with the max() fct
              Rahul Lakhina

              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

                • Re: Trouble with the max() fct
                  Friedrich Hofmann

                  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.

                   

                   

                   

                   

                   

                   

                    • Re: Trouble with the max() fct
                      Friedrich Hofmann

                      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

                        • Re: Re: Trouble with the max() fct
                          Friedrich Hofmann

                          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

                            • Re: Re: Trouble with the max() fct
                              Rahul Lakhina

                              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

                                • Re: Re: Trouble with the max() fct
                                  Friedrich Hofmann

                                  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.

                                    • Re: Re: Trouble with the max() fct
                                      Friedrich Hofmann


                                      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;