Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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])
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.
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
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,
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;
I don't think you must use macro for this. There is function AddMonths. Try
AddMonths([Ult. Cal], Freq) as [PROX. CAL.]