load record selecting only the most recent - date format text

[#1]

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:

CDARML	CLISML	DTUAML	DTFINL	PRZVND	FIELD5	FIELD6
3722	001	20160315	0	12,50	2170	DES1
12470	001	20150415	20160201	43,78	2170	DES2
3722	001	20140426	20160314	11,70	2170	DES1
3400	001	20140614	0	22,00	1000	DES3
2300	001	20150614	0	3,00	2800	DES4
12470	001	20160202	0	42,79	2170	DES2
2300	1240012	20150614	0	7,00	2800	DES4

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:

CDARML	CLISML	DTUAML	DTFINL	PRZVND	FIELD5	FIELD6
3722	001	20160315	0	12,50	2170	DES1
3400	001	20140614	0	22,00	1000	DES3
2300	001	20150614	0	3,00	2800	DES4
12470	001	20160202	0	42,79	2170	DES2

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

CDARML	CLISML	DTUAML	DTFINL	PRZVND	FIELD5	FIELD6
3400	001	20140614	0	22,00	1000	DES3
2300	001	20150614	0	3,00	2800	DES4

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:

CDARML	CLISML	DTUAML	DTFINL	PRZVND	FIELD5	FIELD6
3722	001	20160315	0	12,50	2170	DES1
3400	001	20140614	0	22,00	1000	DES3
2300	001	20150614	0	3,00	2800	DES4
12470	001	20160202	0	42,79	2170	DES2

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

I need help

thanks

giuliano

[sushil353 · Accepted solution]

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

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

[#4]

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

[#5]

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

[#6]

I added more information, see the discussion below

[#7]

‌hi sushil

did you read the further info I wrote?

could you help me again

thanks

giuliano