QVD Period

[pgalvezt]

Hello, Im connected by OLE DB on many databases (Differents folders with many clients and months) to get all thouse connections Im using LOAD *, 'Site' as Source; LOAD *, 'Site1' as Source;LOAD *, 'Site3' as Source; and so on for each connection. Finally I Stored them as BasesTarjetas. Now Im Trying to generate qvds by months because the current qvd is too heavy. Reading in the forum I found this:

http://community.qlik.com/message/200824#200824

I tried to do the same thing with Resident table but it QVD.

Temp:
LOAD  Distinct Month(TDCORTE2) AS MonthName
resident BasesTarjetas;

LET vCount=NoOfRows('Temp');
FOR i=0 to $(vCount)
LET vMonth = Peek('MonthName',$(i),'Temp');

MonthWise:
LOAD *
Resident BasesTarjetas
Where Month(TDCORTE2) = '$(vMonth)';

STORE MonthWise into Month\$(vMonth).qvd;

DROP Table MonthWise;

Next
DROP Table Temp;

!Warning Message Table not found!

Thank you!

[bgerchikov]

If you'd upload source, I'll run it. I've done something like that in the past, so...

[pgalvezt]

Gives a General Error. I put the log

[pgalvezt]

Look, all my databases contains the same tables so Im using this:

Bases:
OLEDB CONNECT TO...... Client 1(Same Client Diferent Mont (April)
LOAD *, 'Site1' as Source;
LOAD
num(`cod_bloq`)      as Cod_Bloqueo,
if(num(`cod_bloq`) <> 8, 'Vigente','Castigada')  as Vigente_Castigo,
left(cuenta,3)      as Clasificacion_Bin,
Num#(left(cuenta,3))&rut     as Homologo_Btcc11,
MonthName(Date(`td_corte2`,'MMM-YY'))   as Periodo,
num(Month(Date(`td_corte2`,'MMM-YYYY')),'00')  as PeriodoNum;
SQL SELECT *
FROM btcc11
Where estado = 1;

OLEDB CONNECT TO...... Client 1(Same Client Diferent Month (may)
LOAD *, 'Site2' as Source;
LOAD
num(`cod_bloq`)      as Cod_Bloqueo,
if(num(`cod_bloq`) <> 8, 'Vigente','Castigada')  as Vigente_Castigo,
left(cuenta,3)      as Clasificacion_Bin,
Num#(left(cuenta,3))&rut     as Homologo_Btcc11,
MonthName(Date(`td_corte2`,'MMM-YY'))   as Periodo,
num(Month(Date(`td_corte2`,'MMM-YYYY')),'00')  as PeriodoNum;
SQL SELECT *
FROM btcc11
Where estado = 1;

Store Bases Into $(PathProdQvd)Bases.qvd;

So QlikView at final generate just one big table call Bases.

After that I put:

Temp:
LOAD  Distinct Month(TDCORTE2) AS MonthName
resident BasesTarjetas;

LET vCount=NoOfRows('Temp');
FOR i=0 to $(vCount)
LET vMonth = Peek('MonthName',$(i),'Temp');

MonthWise:
NoConcatenate
LOAD *
Resident BasesTarjetas
Where Month(TDCORTE2) = '$(vMonth)';

STORE MonthWise into Month\$(vMonth).qvd;

DROP Table MonthWise;

Next
DROP Table Temp

[pgalvezt]

If you need the source I can Choose the smaller client and send you as Excel File.

[bgerchikov · Accepted solution]

Your log shows

STORE MonthWise into Month\abr.qvd

First, remove "Month\" from the path - most likely it causes the problem. Also, use hardcoded path instead of variable ($vMonth) to test it out.

Instead of "Month\" in the path you can use Directory Month;

[pgalvezt]

Thank you now its working. Last question the new qvd they called abr, may,jun. I would like a new format like Tarjetas042012.qvd

Tarjetas052012.qvd

How can I do that?

[pgalvezt]

I Put:

STORE Tarjetas into $(PathProdQvd)Tarjetas$(vMonth).qvd;

Result Tarjetasabr.qvd  Tarjetasmay.qvd

I need Tarjetas042012  Tarjetas052012.qvd

Thank you

[bgerchikov]

So, you need something like:

let vPath = 'Tarjetas' & $(vMonth) & $(vYear) & '.qvd';

STORE Tarjetas into $(vPath);

You have vMonth already, so you need to find out the Year to assign it to vYear variable

[pgalvezt]

I got these cod exactly like you show it. but QlikView must be completely automatically. I don't know if you understand well what exactly what I want but the idea is not put dates into Set sentence. For example if today is end month tomorrow I m going to change 08 by 09 right? I tried this

Temp:
LOAD  Distinct YearName(MonthName(Date(PeriodoNum_Carga,'MMYYYY'))) AS Periodo_Carga
resident BasesTarjetas;

LET vCount=NoOfRows('Temp');
FOR i=0 to $(vCount)
LET vCarga = Peek('Periodo_Carga',$(i),'Temp');

Tarjetas:
NoConcatenate
LOAD *
Resident BasesTarjetas
Where YearName(MonthName(Date(PeriodoNum_Carga,'MMYYYY'))) = '$(vCarga)';

STORE Tarjetas into $(PathProdQvd)Tarjetas$(vCarga).qvd;

DROP Table Tarjetas;

Next
DROP Table Temp;

but now appear Tarjetas2012. I just need appear the month (Tarjetas042012)

[bgerchikov]

Check the value of vCarga variable whether it contains Month ... Maybe you need to add vMonth?

STORE Tarjetas into $(PathProdQvd)Tarjetas$(vCarga)$(vMonth) .qvd;