Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Bom dia,
Estou com dificuldades para entender como posso pegar somente a ultima data do mês, com registro na base, para cada conta.
Em anexo uma planilha com uma amostra. Eu preciso somente dos registros em amarelo. Observem que não é necessariamente o último dia do mês, mas sim a última data registrada.
A ideia é pegar o resultado e gravar em uma tabela.
Desde já agradeço pela atenção.
Ernesto Alves.
Boa tarde,
Acho que pode ser algo assim:
tempTeste:
LOAD CODIGOEMPRESA,
CONTACTB,
DATASALDO,
Num(DATASALDO) as NumData,
text(Date(Num(DATASALDO),'MMM/YY')) as MesAno,
VALORDEB,
VALORCRED
FROM
[..\Downloads\aaaa.xlsx]
(ooxml, embedded labels, table is Result);
Left Join
LOAD max(NumData) as NumData,
1 as flagMaximaData
Resident tempTeste
Group by MesAno;
NoConcatenate
Final:
LOAD *
Resident tempTeste
Where flagMaximaData= 1;
DROP Table tempTeste;
Att,
Sorry, I can't speak Portuguese.
I hope I can help you in English (or Italian, if you prefer).
Load your Excel file
Pay attention: field DATASALDO has to be a date.
Check DateFormat at the start of the script or, in load, you can write:
Date(Date#(DATASALDO,'YYYY-MM-DD')) as DATASALDO.
Now you can calculate End Month Date:
Date(Floor(MonthEnd(Date#(DATASALDO,'YYYY-MM-DD')))) as MONTHLASTDAY
If you want calculate a flag in the script you can compare DATASALDO and MONTHLASTDAY (as defined before) in load:
if(DATASALDO=MONTHLASTDAY,'T','F') as FLAG
In front end, if you filter 'T' in field FLAG you will have only value if DATASALDO is last day of month.
You can operate directly in front end with set analysis in your expression.
For instance, you need only value of VALORDEB (please, assure you loaded VALORDEB as numeric) for which DATASALDO is last day of month. Then you can write:
sum({<DATASALDO={"=(DATASALDO=MONTHLASTDAY)"}>}VALORDEB)
if you defined MONTHLASTDAY in the script.
If you don't have loaded MONTHLASTDAY in the script you can write in Set Analysis
sum({<DATASALDO={"=(DATASALDO=Floor(MonthEnd(DATASALDO)))"}>}VALORDEB)
Please try and give feedback if I understand your question.
Hi paola,
First, thank you for responding!
I understood all of your explanation.
Your idea is great, but it still does not solve my problem.
I'll try to explain better.
I need to run a load script from a QVD as per the example in the attached worksheet.
In this script, check for each CODIGEMEN + CONTACTB, IF DATASALDO is the last record of THAT MONTH and then save the corresponding VALORDEB and VALORCRED in another table.
Based on the attached example, the result of the created table would be the following:
CODIGOEMPRESA | CONTACTB | DATASALDO | VALORDEB | VALORCRED |
599 | 11 | 2017-12-30 | 899749 | 0 |
599 | 11 | 2018-01-31 | 0 | 29931.59 |
599 | 11 | 2018-02-28 | 26698 | 8423.31 |
599 | 11 | 2018-03-29 | 10835.22 | 18478.39 |
599 | 11 | 2018-04-30 | 30938.44 | 4156.24 |
599 | 11 | 2018-05-30 | 21241 | 30537.63 |
599 | 11 | 2018-06-28 | 0 | 5.10 |
Best regards,
I'm sorry I misunderstood.
When you write CODIGEMEN + CONTACTB, do you mean CODIGOEMPRESA+CONTACTB?
Try this in the script:
Temp_aaaa:
LOAD *,
MakeDate(year(DATASALDO),month(DATASALDO),1) as MonthYear,
FROM [lib://YOURPATH/aaaa.xlsx]
(ooxml, embedded labels, table is Result);
MonthLast:
LOAD distinct MonthYear,
CODIGOEMPRESA,
CONTACTB,
Date(max(DATASALDO)) as MonthLast
Resident Temp_aaaa
group by MonthYear,CODIGOEMPRESA,CONTACTB;
New_aaaa:
LOAD CODIGOEMPRESA,
CONTACTB,
DATASALDO,
VALORDEB,
VALORCRED
resident Temp_aaaa
where exists(MonthLast,DATASALDO);
drop table Temp_aaaa,MonthLast;
Hi Paola,
Don´t work.
The results is a table New_aaaa empty.
Script :
Temp_aaaa:
LOAD
CODIGOEMPRESA,
CONTACTB,
DATASALDO,
VALORDEB,
VALORCRED,
MakeDate(year(DATASALDO),month(DATASALDO),1) as MonthYear
FROM [lib://DOCS/aaaa.xlsx]
(ooxml, embedded labels, table is Result);
MonthLast:
LOAD distinct MonthYear,
CODIGOEMPRESA,
CONTACTB,
Date(max(DATASALDO)) as MonthLast
Resident Temp_aaaa
group by MonthYear,CODIGOEMPRESA,CONTACTB;
New_aaaa:
LOAD CODIGOEMPRESA,
CONTACTB,
DATASALDO,
VALORDEB,
VALORCRED
resident Temp_aaaa
where exists(MonthLast,DATASALDO);
drop table Temp_aaaa,MonthLast;
Boa tarde,
Acho que pode ser algo assim:
tempTeste:
LOAD CODIGOEMPRESA,
CONTACTB,
DATASALDO,
Num(DATASALDO) as NumData,
text(Date(Num(DATASALDO),'MMM/YY')) as MesAno,
VALORDEB,
VALORCRED
FROM
[..\Downloads\aaaa.xlsx]
(ooxml, embedded labels, table is Result);
Left Join
LOAD max(NumData) as NumData,
1 as flagMaximaData
Resident tempTeste
Group by MesAno;
NoConcatenate
Final:
LOAD *
Resident tempTeste
Where flagMaximaData= 1;
DROP Table tempTeste;
Att,
Good Morning,
At the beginning, I had an empty "New_aaaa" table as a result, too.
It was only a DateFormat problem.
Please, could you check if DATASALDO has been read as a date?
Paola
Hi Paola,
Yes, DATASALDO is a date.
Thanks,
Bom dia Mauri,
Perfeito ! Deu certo aqui com esse exemplo.
Agora vou testar lendo o BD, mas deve resolver !
Muito obrigado !
Abraços,