6 Replies Latest reply: Sep 24, 2016 4:14 PM by Giuliano Beltramello RSS

    load record selecting only the most recent - date format text

    Giuliano Beltramello

      Hi

      I have this issue: a list of articles (CDARML) with several fields, where one of this (DTUAML) is the starting date of validity of the price, DTFINL is the end validity date of the price, PRZVND is the sale price, and all the other field have other information not of interest in this issue (but I have to load them). Substantially the situation is the following:

       

      CDARMLCLISMLDTUAMLDTFINLPRZVNDFIELD5FIELD6

      3722

      00120160315012,502170DES1
      12470001201504152016020143,782170DES2
      3722001201404262016031411,702170DES1
      340000120140614022,001000DES3
      23000012015061403,002800DES4
      1247000120160202042,792170DES2
      230012400122015061407,002800DES4

       

      The problem now is that I have to load (in the script), for each article, only those that have the most recent starting date of validity of the price. So:

            

      CDARMLCLISMLDTUAMLDTFINLPRZVNDFIELD5FIELD6

      3722

      00120160315012,502170DES1
      340000120140614022,001000DES3
      23000012015061403,002800DES4
      1247000120160202042,792170DES2

       

      I wrote this script:

      LOAD

           CDARML,

           DATE((DATE#([DTUAML], 'YYYYMMDD')), 'DD/MM/YYYY') AS DDATE,

           CLISML,

           DTFINL,

           PRZVND,

           FIELD5,

           FIELD6

       

      FROM TABLE

      WHERE (DTFINL = 0 AND CLISML = 0)

       

      But the result is this

      CDARMLCLISMLDTUAMLDTFINLPRZVNDFIELD5FIELD6
      340000120140614022,001000DES3
      23000012015061403,002800DES4

       

      FURTHERMORE, if I try to put the expression MAX  (Max(Date((Date#([DTUAML] ...etc) qlikview return me an expression error.

      I don't know how to proceed, but, if someone can help me I would load the most recent price for each record like this:

       

      CDARMLCLISMLDTUAMLDTFINLPRZVNDFIELD5FIELD6

      3722

      00120160315012,502170DES1
      340000120140614022,001000DES3
      23000012015061403,002800DES4
      1247000120160202042,792170DES2

       

      I googled a lot, but I didn't find a solution that mach my situation.

      I need help

      thanks

      giuliano

        • Re: load record selecting only the most recent - date format text
          Sushil Kumar

          Hi,

           

          If you are using the max function on top of your date field.. then you have to aggregate it using group by on some field.

           

          this must be something like:

           

          Load

          Max(Date) as maxdate

          from table

          group by CDARML;

           

          I am not clear with your logic.. to get the latest value.. for example

           

          article no 2300 has same date, on what base you are having 3 PRZVND

           

          HTH

          Sushil

            • Re: load record selecting only the most recent - date format text
              Giuliano Beltramello

              Hi,

              I tried you suggestion, but qlikvew return an error for INVALID EXPRESSION ... please see the following script (of course there are much more fields that at the beginning of the discussion I didn't mentioned 'cause not of your interest, or at least, not of interest for the discussion); furthermore the old DTFINL now is DTLVML  and the old PRZVND is now PRZLML2 (sorry, but when I posted the question I was out of office and I wrote from memory!)

               

              Invalid expression

              LOAD TIREML,

                   PROFML,

                   DT01ML,

                   DTMNML,

                   CDDTML,

                   CLISML,

                   CDVAML,

                   CDARML,

                   CTGMML,

                   CLASML,

                   CDCIML,

                   CDFAML,

                   CDMAML,

                   MAX (Date ( (Date#([DTUAML],'YYYYMMDD')), 'DD/MM/YYYY')) AS LASTDATE,

                   DTLVML,

                   PRZLML,

                   PRZLML2,

                   PR1LML,

                   PR2LML,

                   FLPPML,

                   TIPRML,

                   NOPRML,

                   AUPRML,

                   AUPRML2,

                   FAUPML,

                   FZAUML,

                   SCCAML,

                   SCCAML2,

                   FSCCML,

                   FZCAML,

                   DTICML,

                   DTFCML,

                   AS01ML,

                   AS01ML2,

                   FAU1ML,

                   AS02ML,

                   AS02ML2,

                   FAU2ML,

                   AS03ML,

                   AS03ML2,

                   FAU3ML,

                   AS04ML,

                   AS04ML2,

                   FAU4ML,

                   AS05ML,

                   AS05ML2,

                   FAU5ML,

                   FAUSML,

                   FANOML,

                   TAO1ML,

                   TAO2ML,

                   TAO3ML,

                   TAO4ML,

                   TAO5ML,

                   TAO6ML,

                   SCT1ML,

                   SCT2ML,

                   SCT3ML,

                   SCT4ML,

                   SCT5ML,

                   SCT6ML,

                   SCT1ML2,

                   SCT2ML2,

                   SCT3ML2,

                   SCT4ML2,

                   SCT5ML2,

                   SCT6ML2,

                   FTAOML,

                   FZTAML,

                   CDVVML,

                   CMBVML,

                   SCOPML,

                   SCOMML,

                   MOCSML,

                   CDG1ML,

                   CDG2ML,

                   CDG3ML

              FROM

              [\\psf\Home\Desktop\stp mov magazzino\ANALISI_MAG_FER\FER60DAT_MGLIS01F.txt]

              (txt, codepage is 1252, embedded labels, delimiter is ';')

              WHERE(CLISML = 001 And DTLVML = 0)

              GROUP BY ([CDARML])

              • Re: load record selecting only the most recent - date format text
                Giuliano Beltramello

                I added more information, see the discussion below

                • Re: load record selecting only the most recent - date format text
                  Giuliano Beltramello

                  hi sushil

                  did you read the further info I wrote?

                  could you help me again

                  thanks

                  giuliano

                • Re: load record selecting only the most recent - date format text
                  Anil Babu Samineni

                  You've written where condition. But In you table you don't have CLISML zero. Check that.

                   

                  For max data you can simply use

                   

                  Max(DTUAML)

                  • Re: load record selecting only the most recent - date format text
                    Giuliano Beltramello

                    Anil, you are right, I'm sorry, I copied in my question the wrong code: the Where condition is "Where (DTFINL = 0 AND CLISML = 001).

                    Sushil .... I explain the logic: CLISML identify the price list, where 001 is the purchase price, while 1240xxx is the n sale price list (n because it could happen that different client has different discount, so different price). I'm interested only to the purchase price, that is why I put the condition Where CLISML = 001.

                    Then, as regard date, when we insert a new article, let suppose 12470, the system database record the input date (in my example DTUAML 20150415, and at the same time the final date of validity DTFINL of that price is 0); next time we input a variation of the price, the original final date of validity DTFINL  is updated with the new input, in my example 20160201, and a new recod is created with the new price and its starting date ( 20160202)  togethere the final date ( DTFINL 0).... in the example table I wrote you see the updated situation as above described.

                    So if all the above is clear (sorry for my bad english), it would be clear that my purpose is to load, for each article CDARML, the most recent purchase price (price list CLISML 001), and the most recent has DTFINL = 001 and younger starting date

                    In any case now I try to group as suggested by Sushil

                    will let you now shortly

                    giuliano