topic Load Dates in QlikView

Load Dates

pgalvezt — Mon, 18 Jun 2012 18:11:49 GMT

Hi, I had connected QlikView in our ERP System and I need to extract periods like May-2011.qvd And Jun-2011.qvd (into 2 qvds) Automatically.

let xPeriodo = date(today()+(day(today())-14));
let xxPeriodo = date(MonthStart(today(),-13));

TABLE1:
LOAD
Year(Date#(PERIOD_NAME,'MMM-YYYY'))&
num(Month(Date#(PERIOD_NAME,'MMM-YYYY')),'00')&
PERIOD_NAME,
Year(Date#(PERIOD_NAME,'MMM-YYYY'))    As Año,
Month(Date#(PERIOD_NAME,'MMM-YYYY'))   As Mes,
MonthName(Date#(PERIOD_NAME,'MMM-YYYY')) As Periodo,
Year(Date#(PERIOD_NAME,'MMM-YYYY'))&
num(Month(Date#(PERIOD_NAME,'MMM-YYYY')),'00') As PeriodoNum,
SALDO;
SQL
From
XXSOCD_GL_SALDOS_APERTURA sa,
XXSOCD_GL_COMBINACIONES_CONT co
where
    (PERIOD_NAME)>= '$(xxPeriodo)' and (PERIOD_NAME)<= '$(xPeriodo)' And
    //(sa.PERIOD_NAME)>= ('$(xxPeriodo)') and (sa.PERIOD_NAME)<= ('$(xPeriodo)') And
//date(sa.PERIOD_NAME)>=date('$(xxPeriodo)') and date(sa.PERIOD_NAME)<=date('$(xPeriodo)') And
//sa.PERIOD_NAME = '$(xxPeriodo) And'
ID;

Store TABLE1 Into $(PathPruebasGL)TABLE1$(xPeriodo).qvd;
Store TABLE1 Into $(PathPruebasGL)TABLE1$(xxPeriodo).qvd;
Drop Table A;

The error that QV thrown me is Failed Script. Is this possible to do in QlikView?

Thank you for your help.

Re: Load Dates

chematos — Mon, 18 Jun 2012 18:25:22 GMT

Hi,

where is the Select sentence?? SQL Select * From.....

What is the format of PERIOD_NAME ??

SQL

From

XXSOCD_GL_SALDOS_APERTURA sa,

XXSOCD_GL_COMBINACIONES_CONT co

where

Re: Load Dates

pgalvezt — Mon, 18 Jun 2012 18:41:26 GMT

SQL
select
sa.*,
ID,

The format is:

num(Month(Date#(PERIOD_NAME,'MMM-YYYY')),'00')

Re: Load Dates

pgalvezt — Mon, 18 Jun 2012 19:33:12 GMT

If I put

Let xPeriodo = 'JUN-2011';

Let xxPeriodo = 'MAY-2011';

Just Jun is loaded. But not May. Is there any way to extract in the same time those 2 datatimes?

Thanks,

Re: Load Dates

chematos — Mon, 18 Jun 2012 19:42:13 GMT

Must be a sql error, because the Load sentence of QV only get the data that is read from the SQL, so if there is no data of May is because is not bringing from SQL.

Have you run your SQL sentence on your database system with that periods as conditions?

Is working fine?

In the load sentece, I recommend you to read all the information and after that, create both tables with where conditions.

Jun-11:

Load *,

resident TABLE1

where PeriodNum = 06;

This is a static option for testing

Re: Load Dates

pgalvezt — Mon, 18 Jun 2012 20:09:22 GMT

Thank you for you reply,

I have checked the 2 months load one bye one and every month has information. The thing is I can´t read all the information because is too much. The server is going to collapse if I do that. If anyone know the way to extract this 2 months no matters how I will thank you.

Re: Load Dates

chematos — Mon, 18 Jun 2012 20:13:46 GMT

Why don´t you make two charges?? One for each month, so you don´t collapse the server.

let xPeriodo = date(today()+(day(today())-14));
let xxPeriodo = date(MonthStart(today(),-13));

TABLE1:
LOAD
...,

...,
SALDO;
SQL
From
XXSOCD_GL_SALDOS_APERTURA sa,
XXSOCD_GL_COMBINACIONES_CONT co
where
(PERIOD_NAME)= '$(xxPeriodo)'
...;

Store TABLE1 Into $(PathPruebasGL)TABLE1$(xxPeriodo).qvd;
Drop Table A;

TABLE2:

NoConcatenate
LOAD
...,

...,
SALDO;
SQL
From
XXSOCD_GL_SALDOS_APERTURA sa,
XXSOCD_GL_COMBINACIONES_CONT co
where
(PERIOD_NAME)= '$(xPeriodo)'
...;

Store TABLE1 Into $(PathPruebasGL)TABLE1$(xPeriodo).qvd;
Drop Table A;

Store TABLE2 Into $(PathPruebasGL)TABLE2$(xPeriodo).qvd;

Re: Load Dates

pgalvezt — Tue, 19 Jun 2012 15:28:30 GMT

Many Thanks...