Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to build a variable for current datamonth

i need to pull the data from database for the current data month - 2 so for this i need to automate the process

so in the script i need to give month= 8/01/2013  this should change every month

can any one help me in building the variable

thanks in advance

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

LET vData = date(monthstart(addmonths(Today(),-2)));

View solution in original post

10 Replies
Clever_Anjos
Employee
Employee

LET vData = date(monthstart(addmonths(Today(),-2)));

Not applicable
Author

thank you very much Anjos

Clever_Anjos
Employee
Employee

you´re welcome

javier_florian
Creator III
Creator III

Hi Apache77,

If you are loading data, set variable according db engage:

1. flat file (xls, csv,...) -> Month(Date(Date_Field_Database, 'MM/DD/YYYY')) <= Month(today())-2;

2. sql server -> Convert(Datetime, Date_Field_Database) >= Convert(Datetime, Dateadd(month,-2,getdate());

But, if you have an object, you can refer to Clever's answer.

Regards,

JFlorian

Not applicable
Author

when we do this my qvd will have only current month data right

if i reload it in next month then i will have the data of September but i will loose data of august

now if i dont want to loose the data which i loaded last month what will be the condition

Clever_Anjos
Employee
Employee

Check your manual for "Incremental Load", there´s what you need

Not applicable
Author

is there a way you can give me small script

i went through it but unable to make it

Clever_Anjos
Employee
Employee

LET Ontem = date(Today()-1,'YYYY-MM-DD');

Contratacao:

SQL SELECT

  "COD_ANALISTA_CONTRATADO" as "Contratacao.COD_ANA_CONTR",

    "COD_CONTRATACAO" as "Contratacao.COD_CONTRATACAO",

    "COD_DEMANDA" as "Contratacao.COD_DEMANDA",

    "COD_EMPRESA_CONTRATADA" as "Contratacao.COD_EMP_CONTR",

    "COD_USR_INCLUSAO" as "Contratacao.COD_USR_INCLUSAO",

    "DAT_FIM_DESENVOLVIMENTO" as "Contratacao.DAT_FIM_DESENV",

    "DAT_INCLUSAO" as "Contratacao.DAT_INCLUSAO",

    "DAT_INICIO_DESENVOLVIMENTO" as "Contratacao.DAT_INI_DESENV",

    "DES_CONTRATACAO" as "Contratacao.DES_CONTRATACAO",

    "FLG_BANCO_HORAS" as "Contratacao.FLG_BANCO_HORAS",

    "TOTAL_HORAS_CONTRATADAS" as "Contratacao.TOTAL_H_CONTR",

    "VALOR_HOMOLOGACAO" as "Contratacao.VALOR_HOMO",

    "VALOR_HORA_CONTRATADO" as "Contratacao.VALOR_H_CONTR",

    "VALOR_IMPLANTACAO" as "Contratacao.VALOR_IMPLANT",

    "VALOR_INFRA_ESTRUTURA" as "Contratacao.VALOR_INFRA",

    "VALOR_OUTROS" as "Contratacao.VALOR_OUTROS",

    "VALOR_TOTAL_CONTRATADO" as "Contratacao.VALOR_TOT_CONTR",

    "VALOR_TREINAMENTO" as "Contratacao.VALOR_TREINAMENTO"

FROM Contratacao

WHERE

  DAT_INCLUSAO >= '$(Ontem)';

Concatenate

LOAD Contratacao.COD_ANA_CONTR,

     Contratacao.COD_CONTRATACAO,

     Contratacao.COD_DEMANDA,

     Contratacao.COD_EMP_CONTR,

     Contratacao.COD_USR_INCLUSAO,

     Contratacao.DAT_FIM_DESENV,

     Contratacao.DAT_INCLUSAO,

     Contratacao.DAT_INI_DESENV,

     Contratacao.DES_CONTRATACAO,

     Contratacao.FLG_BANCO_HORAS,

     Contratacao.TOTAL_H_CONTR,

     Contratacao.VALOR_HOMO,

     Contratacao.VALOR_H_CONTR,

     Contratacao.VALOR_IMPLANT,

     Contratacao.VALOR_INFRA,

     Contratacao.VALOR_OUTROS,

     Contratacao.VALOR_TOT_CONTR,

     Contratacao.VALOR_TREINAMENTO

FROM $(Path)\Contratacao.qvd (qvd)

WHERE NOT Exists(Contratacao.COD_CONTRATACAO);

STORE Contratacao INTO $(Path)\Contratacao.qvd;

DROP TABLE Contratacao;

Not applicable
Author

can you please help me is this for incremental load