Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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)
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
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])
I added more information, see the discussion below
hi sushil
did you read the further info I wrote?
could you help me again
thanks
giuliano