topic Re: how to build a variable for current datamonth in QlikView

how to build a variable for current datamonth

Fri, 18 Oct 2013 20:03:13 GMT

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

Re: how to build a variable for current datamonth

Clever_Anjos — Fri, 18 Oct 2013 20:04:43 GMT

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

Re: how to build a variable for current datamonth

Fri, 18 Oct 2013 20:13:19 GMT

thank you very much Anjos

Re: how to build a variable for current datamonth

Clever_Anjos — Fri, 18 Oct 2013 20:14:08 GMT

you´re welcome

Re: how to build a variable for current datamonth

javier_florian — Fri, 18 Oct 2013 20:17:31 GMT

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

Re: how to build a variable for current datamonth

Fri, 18 Oct 2013 20:18:42 GMT

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

Re: how to build a variable for current datamonth

Clever_Anjos — Fri, 18 Oct 2013 20:21:18 GMT

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

Re: how to build a variable for current datamonth

Fri, 18 Oct 2013 20:43:52 GMT

is there a way you can give me small script

i went through it but unable to make it

Re: Re: how to build a variable for current datamonth

Clever_Anjos — Mon, 21 Oct 2013 11:22:26 GMT

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;

Re: how to build a variable for current datamonth

Mon, 21 Oct 2013 19:43:26 GMT

can you please help me is this for incremental load

Re: how to build a variable for current datamonth

Clever_Anjos — Mon, 21 Oct 2013 19:46:23 GMT

Yes, it´s an example of incremental load based on a DateTime