Load Dates

[pgalvezt]

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.

[chematos]

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

[pgalvezt]

SQL
select
sa.*,
ID,

The format is:

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

[pgalvezt]

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,

[chematos]

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

[pgalvezt]

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.

[chematos · Accepted solution]

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;

[pgalvezt]

Many Thanks...