Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

load record selecting only the most recent - date format text

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

1 Solution

Accepted Solutions
sushil353
Honored Contributor II

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

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

6 Replies
sushil353
Honored Contributor II

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

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

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)

Life is so rich, and we need to respect to the life !!!
Not applicable

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

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

Not applicable

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

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])

Not applicable

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

I added more information, see the discussion below

Not applicable

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

‌hi sushil

did you read the further info I wrote?

could you help me again

thanks

giuliano

Community Browser