11 Replies Latest reply: Feb 15, 2016 9:14 AM by Luc CELESTIN RSS

    Max Date issue

    Luc CELESTIN

      I have a problem with Max Date functionality. I have a file with Items and costing dates. I would like to get the last Costing date for the different items.

       

      I would like to do it in the script, but function MAX is not ok. I get error message.

       

      My Qlikview version is connected to Movex/M3

       

      MCCOMA: // Load Max Date

       

      LOAD

       

      KPCONO AS [KPCONO - Company],

      KPFACI as [KPFACI - Facility],

      TEXT(KPITNO) as [IA_EZ_Item Number],

      KPSTRT as [KPSTRT - Product structure type],

      KPPCTP as [KPPCTP - Costing type],

      Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') as [KPPCDT - Costing date],

       

      I have tried Max formula both in script and in Chart but I have always errors coming in.

       

      in my script, for date format, I have

       

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='YYYY-MM-DD';

       

      Thanks for your help

        • Re: Max Date issue
          Srikanth P

          Can you please share your full script snippet.

            • Re: Max Date issue
              Luc CELESTIN

              I have done this

               

              MCCOMA: // Load Max Date

               

              LOAD

               

              KPCONO AS [KPCONO - Company],

              KPFACI as [KPFACI - Facility],

              TEXT(KPITNO) as [IA_EZ_Item Number],

              KPSTRT as [KPSTRT - Product structure type],

              KPPCTP as [KPPCTP - Costing type],

              Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') as [KPPCDT - Costing date],

              KPCA01 as [KPCA01 - Value Component A01],

              KPCA02 as [KPCA02 - Value Component A02],

              KPCA03 as [KPCA03 - Value Component A03],

              KPCB01 as [KPCB01 - Value Component B01],

              KPCB02 as [KPCB02 - Value Component B02],

              KPCB03 as [KPCB03 - Value Component B03],

              KPCB04 as [KPCB04 - Value Component B04]

              ;

               

              SQL SELECT KPCONO, KPFACI, KPITNO, KPSTRT, KPPCTP, KPPCDT, KPCA01, KPCA02, KPCA03, KPCB01, KPCB02, KPCB03, KPCB04

              FROM S658685C.MVXCDTA020.MCCOMA

              WHERE KPCONO = 020 AND KPFACI = 'HUP' AND KPPCTP = '3' AND KPPCDT >= '20160101'

              ORDER BY KPCONO, KPFACI,KPITNO,KPSTRT ;

            • Re: Max Date issue
              Sunny Talwar

              What is the exact error you are seeing?

                • Re: Max Date issue
                  Luc CELESTIN

                  Every time, I put Max(Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD')), I get the message

                   

                  "Invalid Expression" and the file is not loaded

                    • Re: Max Date issue
                      Sunny Talwar

                      That is because aggregation functions such as Max, Sum, Avg need Group By statement to aggregate date. Are you trying to find the max for the whole date or by certain field such as max date by country for example?

                        • Re: Max Date issue
                          Luc CELESTIN

                          in fact I want to get the max date by item.

                           

                          In this file we have

                          Item     Date     Cost

                          A     20150101      10

                          A     20151201        9

                          A     20160101        8

                           

                          I would like the last one

                            • Re: Max Date issue
                              Sunny Talwar

                              So something like this:

                               

                              Table:

                              LOAD Item,

                                        Date,

                                        Cost

                              FROM Source;

                               

                              Right Join(Table)

                              LOAD Item,

                                        Max(Date) as Date

                              Resident Table

                              Group By Item;

                               

                              Thanks for pointing out the error Sébastien

                                • Re: Max Date issue
                                  Sébastien Fatoux

                                  Sunny, you need to group by Item, not by Table.

                                    • Re: Max Date issue
                                      Sunny Talwar

                                      Hahahaha you are right

                                        • Re: Max Date issue
                                          Luc CELESTIN

                                          Hello

                                           

                                          Thanks all for your answers.

                                           

                                          I will try on Monday and I will let you know.

                                           

                                          Have a nice week-end

                                           

                                          Luc

                                          • Re: Max Date issue
                                            Luc CELESTIN

                                            Hello

                                            Thanks for your help

                                             

                                            I was finally able to get the proper extract, I have now this

                                             

                                            MCCOMA: // Load Max Date

                                             

                                            LOAD

                                             

                                            KPFACI & '|' & TEXT(KPITNO) & '|' & Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') AS KEY_MCCOMA_MCCOMA2,

                                            KPCONO ,

                                            KPFACI ,

                                            TEXT(KPITNO) AS [IA_EZ_Item Number],

                                            KPSTRT ,

                                            KPPCTP ,

                                            KPPCDT as Date,

                                            Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') AS Date_2,

                                            KPCA01 ,

                                            KPCA02 ,

                                            KPCA03 ,

                                            KPCB01 ,

                                            KPCB02 ,

                                            KPCB03 ,

                                            KPCB04 ;

                                             

                                            SQL SELECT KPCONO, KPFACI, KPITNO, KPSTRT, KPPCTP, KPPCDT, KPCA01, KPCA02, KPCA03, KPCB01, KPCB02, KPCB03, KPCB04

                                            FROM S658685C.MVXCDTA020.MCCOMA

                                            WHERE KPCONO = 020 AND KPFACI = 'HUP' AND KPPCTP = '3'

                                            ORDER BY KPCONO,KPFACI,KPITNO,KPSTRT ;

                                             

                                            MCCOMAMaxDate: //

                                             

                                            RIGHT KEEP (MCCOMA)

                                            LOAD

                                             

                                            KPFACI & '|' & [IA_EZ_Item Number] & '|' & MAX(Date(Date#([Date],'YYYYMMDD'),'YYYY-MM-DD')) AS KEY_MCCOMA_MCCOMA2,

                                            KPFACI as [KPFACI - Facility],

                                            [IA_EZ_Item Number] as [IA_EZ_Item Number_1],

                                            MAX(Date(Date#([Date],'YYYYMMDD'),'YYYY-MM-DD')) as [KPPCDT - Costing date]

                                             

                                            RESIDENT MCCOMA

                                            GROUP BY KPFACI,[IA_EZ_Item Number];

                                             

                                            It works fine now

                                             

                                            Have a nice week

                                            Luc