topic Re: QVD Period in QlikView

QVD Period

pgalvezt — Wed, 08 Aug 2012 15:40:42 GMT

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!

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 16:59:50 GMT

Any Help?

Thanks

Re: QVD Period

Wed, 08 Aug 2012 17:06:44 GMT

He,

In these situations it's handy to use the debug mode of qlikview en animate the process. This way you can exactly see what all the values of you variables are at that moment.

If you can't solve it that way, you could try the following code

LET rowText = NoOfRows('Temp');

let i=1;

let vMonth = FieldValue('MonthName',$(i));

do while i <=$(rowText)

let text = FieldValue('MonthName',$(i));

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

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

DROP Table MonthWise;

i=i+1;

loop;

DROP Table Temp;

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 17:46:09 GMT

Thank you for your reply. Im having problems with table Wisemonth. Maybe the table doesn't exist. I left a image.

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 19:25:23 GMT

Any Help?

Thanks

Re: QVD Period

bgerchikov — Wed, 08 Aug 2012 19:46:52 GMT

Add NoConcatenate statement:

LET

rowText = NoOfRows('Temp');

   let i=1;

          let vMonth = FieldValue('MonthName',$(i));
do while i <=$(rowText)

      let text = FieldValue('MonthName',$(i));
          MonthWise:

          NoConcatenate

          LOAD *
          Resident BasesTarjetas
          Where Month(TDCORTE2) = '$(text )';

          STORE MonthWise into Month\$(text ).qvd;
          DROP Table MonthWise;

i=i+1;

loop;

DROP Table Temp;

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 20:05:13 GMT

NoConcatenate seems work but finally QlikView give error (Faild Script).

Any Ideas?

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 20:43:03 GMT

Maybe Im Trying to replicate a script for full QVD. In this case I have many tables but with the same information. So I have a one big tables called BasesTarjetas. Is for this reason Im using Resident.

Re: QVD Period

bgerchikov — Wed, 08 Aug 2012 21:50:04 GMT

You might need to check the log file to see the error. It could be security problem as well. Try to cleenup your output directory and run it again.

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 22:09:17 GMT

If you run this QlikView works for you?

Re: QVD Period

bgerchikov — Wed, 08 Aug 2012 22:16:26 GMT

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

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 22:17:59 GMT

Gives a General Error. I put the log

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 22:33:46 GMT

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

Re: QVD Period

pgalvezt — Wed, 08 Aug 2012 22:35:04 GMT

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

Re: QVD Period

bgerchikov — Wed, 08 Aug 2012 22:44:36 GMT

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;

Re: QVD Period

pgalvezt — Thu, 09 Aug 2012 14:31:54 GMT

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?

Re: QVD Period

pgalvezt — Thu, 09 Aug 2012 14:45:50 GMT

I Put:

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

Result Tarjetasabr.qvd Tarjetasmay.qvd

I need Tarjetas042012 Tarjetas052012.qvd

Thank you

Re: QVD Period

bgerchikov — Thu, 09 Aug 2012 15:22:08 GMT

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

Re: QVD Period

pgalvezt — Thu, 09 Aug 2012 15:36:49 GMT

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)

Re: QVD Period

bgerchikov — Thu, 09 Aug 2012 15:48:13 GMT

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

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