Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ernestolmalves
New Contributor III

Buscar somente ultima data do mês com informação

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.

1 Solution

Accepted Solutions
kawamuram
Contributor II

Re: Buscar somente ultima data do mês com informação

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,

8 Replies
paola_valenti
New Contributor III

Re: Buscar somente ultima data do mês com informação

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.

ernestolmalves
New Contributor III

Re: Buscar somente ultima data do mês com informação

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:

     

CODIGOEMPRESACONTACTBDATASALDOVALORDEBVALORCRED
599112017-12-308997490
599112018-01-31029931.59
599112018-02-28266988423.31
599112018-03-2910835.2218478.39
599112018-04-3030938.444156.24
599112018-05-302124130537.63
599112018-06-2805.10

Best regards,

paola_valenti
New Contributor III

Re: Buscar somente ultima data do mês com informação

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;

ernestolmalves
New Contributor III

Re: Buscar somente ultima data do mês com informação

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;

kawamuram
Contributor II

Re: Buscar somente ultima data do mês com informação

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,

paola_valenti
New Contributor III

Re: Buscar somente ultima data do mês com informação

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

ernestolmalves
New Contributor III

Re: Buscar somente ultima data do mês com informação

Hi Paola,

Yes, DATASALDO is a date.

Thanks,

ernestolmalves
New Contributor III

Re: Buscar somente ultima data do mês com informação

Bom dia Mauri,

Perfeito ! Deu certo aqui com esse exemplo.

Agora vou testar lendo o BD, mas deve resolver !

Muito obrigado !

Abraços,