topic question about max data in QlikView

question about max data

Mon, 04 Oct 2010 16:09:13 GMT

Dear experts,

Probebly an easy on for you but I am pretty new with this and I haven't found answer here yet so I was hoping somebody could help me with this.

I have Client and model of machine and i make calibration many time for this machine so i need to make table and get the last data on my table not the history just 1 row show me the last data

my table will be look like that

Client --> model --> last data

thanks

question about max data

Mon, 04 Oct 2010 16:25:48 GMT

I assume Client and Model are the dimensions. For last data, is there a date associated? What is that field called? What is the name of the field you want to display?

Something like this:

only({<DateField={'$(=Aggr(Max(DateField), Client, Model))'}>} [last_date])

question about max data

syed_muzammil — Mon, 04 Oct 2010 16:28:29 GMT

Hi,

On which feild are you differentiating between the latest and the old data?

Order by descending on that field and select the first record.

If you dont have one then jus add an extra field while load, having a unique value for each record and select the record which has tha max value of that extra field.

Regards,

Syed.

question about max data

Mon, 04 Oct 2010 17:38:02 GMT

let me explane much and thanks for this fast replay ....

i connect to oracle database and i have 2 tables [clients, Machines]

each client send to me some models and we make work for many years so i insert everytime the same model machine with new data.

when i run my data inside table its gave me all data "history" of the same model ... so my situation today i not need the history i need the latest data that model enter

for example:

Company Machine Model data

Company1 sony D3 10/2008

Company1 sony D3 10/2010

so now i need my table give me just the latest date not the history

Company Machine Model data

Company1 sony D3 10/2010

question about max data

Anonymous — Mon, 04 Oct 2010 17:58:48 GMT

Not sure I understand the question correctly... It loos like you need this

LOAD
Company,
Machine,
Model,
max(date) as LastDate
RESIDENT...
GROUP BY Company, Machine, Model,

question about max data

Mon, 04 Oct 2010 20:17:26 GMT

thank you very much Michael Solomovich I solve it and i have other situation i think you can solve it also I´m sure its small for you

i already put the code for you to check it ... my problem is the field "Freq" cant read .. i need to know why
feild "Freq" is a numbers 1,5,9 and i want to add that number addition to the month on feild "[Ult. Cal]"
so if the data on field "[Ult. Cal]" like: 22/12/2009 + "Freq" like: 5 so the result going to be 22/5/2010 and i called that field"[PROX. CAL.]"

my macro :

Function vbProximaCalibracao(Data, Frequencia)
Ano = year(Data)
Mes = month(Data)
for i = 1 to Frequencia
if (Mes >= 12 )then
Mes=1
Ano=Ano+1
else
Mes=Mes+1
end if
next
vbProximaCalibracao = Mes & "/"& Ano
end Function

my code :
[Cliente]:
Load CFIDCODIGO as [Cod. Cli],
CFTXNOMEF as Cliente;
sql select *
FROM SAG."CF_CLIENTEFORNECEDOR";

left join

Load
Date(CCDTDTCERT) as [Data Cal],
CCTXCERT as Certificado,
CCTXTAG as Tag_,
CFIDCODIGO as [Cod. Cli],
INIDCODIGO as [Cod. Inst],
NCIDCODIGO as [Nº de Controle];
sql select *
FROM SAG."CC_CERTIFICADOCALIB";

left join

Load
ECNMFREQ as Freq,
NCIDCODIGO as [Nº de Controle];
SQL Select *

FROM SAG."EC_ENTRADACALIBRACAO";

Ult:
load
max([Data Cal]) as [Ult. Cal],
Tag_,
Cliente
resident [Cliente] group by Cliente, Tag_;

load
Freq,
vbproximacalibracao([Ult. Cal], Freq) as [PROX. CAL.]
Resident Ult;

question about max data

Anonymous — Mon, 04 Oct 2010 21:30:53 GMT

I don't think you must use macro for this. There is function AddMonths. Try
AddMonths([Ult. Cal], Freq) as [PROX. CAL.]