topic Re: Re: Re: QVD Distribution according to Period in QlikView

QVD Distribution according to Period

kalyandg — Wed, 19 Feb 2014 11:02:19 GMT

Hi all,

I face much more reloading time due to huge amount of data. We planned to implement Incremental Load. But it was not necessary for my client, because they are updating db three months once.

So I need to seperate the data from db as

Table_201101-201310 -- History(I wont reload it once again)

Table_ 201310-201402 -- (Reload this upto Feb end)

......vice versa

we can use variable to proceed for the next period, but user have to change the period in Qlikview by changing the value in Input box.

But this kind of method was not agreed by my client.

So I planned to use notepad, where i could enter the period no. and use tat notepad in our script, i will provide that notepad to my client and ask them to change the period no. so tat we can get next period data automatically without changing our script.

Is it possible to do using above plan? if yes, please help me how to do with the scripting!

Thanks in Advance,

Best Regards,

Kalyan.D

Re: QVD Distribution according to Period

Peter_Cammaert — Wed, 19 Feb 2014 11:24:22 GMT

Why does the user have to change the period? There are no user configurable reloads in QVS...

Can this not be done automatically, as in loading everything from a fixed or sliding start date up until MonthEnd(today()) as an increment?

Best,

Peter

Re: QVD Distribution according to Period

fernando_tonial — Wed, 19 Feb 2014 11:29:39 GMT

You try this:

LET vMesInicial = Date('01/01/2014');
LET vMesFinal     = Date(MonthEnd(Today())-2);

DATES:
LOAD
    Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMMDD')                 AS DiaIni,
    Date(MonthEnd(AddMonths('$(vMesInicial)',RowNo()-1)),'YYYYMMDD')     AS DiaFim,
    Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMM')                 AS AnoMes
AutoGenerate(Ceil((vMesFinal-vMesInicial)/30.5));

//EXIT Script;

FOR D=1 TO (NoOfRows('DATES'))

    LET vAnoMes = Peek('AnoMes',$(D)-1);
    LET vDiaIni = Peek('DiaIni',$(D)-1);
    LET vDiaFim = Peek('DiaFim',$(D)-1);
   
    TRACE $(vAnoMes);
    [Table]:
    SQL Select * from Table where MonthYear = '$(vAnoMes)';
   
    Store [Table] into DirExtraidos\Table_$(vAnoMes).QVD;
    Drop table 
;
   
NEXT
DROP Table DATES;
Best Regards

Re: QVD Distribution according to Period

datanibbler — Wed, 19 Feb 2014 11:34:42 GMT

Hi Kalyan,

generally, I would always try to use as few distinct elements as possible - so I would suggest not using Notepad or any other external tool, but only the qvd files themselves.

- So every qvd should hold five months' worth of data, yes?

- I assume you already have created that archive_qvd that goes up to 201310, yes?

=> Then my suggestion would be this:

- Find out how to load the file_names of all the files in the directory where those qvd files should be (unfortunately I don't know how to do that, but (not ideally, but alternatively) you might use your .txt approach there, as a qvs file to be populated by the customer and as a last resort you can always hard-code them in an Ínline table)

- Get the largest second ("to") year-month figure from all of those files

- Compare that to the current year-month

- If the current year-month is still within the scope of that file, you can use it
=> the new data should be appended to that qvd

- If the current year-month is not in the scope of that file anymore, you have to create a new one
=> First, LOAD RESIDENT (from some other table, doesn't matter) an empty table, just to have the fields

=> then append the new data to that

HTH

Best regards,

DataNibbler

Re: QVD Distribution according to Period

kalyandg — Wed, 19 Feb 2014 11:47:32 GMT

Hi Fernando,

Thanks for replying me.

I will get error for where condition, because Monthyear field is not in table.
So I would get Field Not Found Error.

Best Regards,

Kalyan.D

Re: Re: QVD Distribution according to Period

fernando_tonial — Wed, 19 Feb 2014 12:02:04 GMT

Do you have chage this part of the script for your script.

[Table]: 
SQL Select * from Table where MonthYear = '$(vAnoMes)';

Store Table into DirExtraidos\Table_$(vAnoMes).QVD;
Drop Table;

[Your Table Name]: 
SQL Select * from YourTableName where YourFildWithPeriod = '$(vAnoMes)';

Store [Your Table Name] indo [Your Table Name]_$(vAnoMes).qvd;
Drop [Your Table Name];

Best Regards.

Tonial.

Re: Re: QVD Distribution according to Period

kalyandg — Wed, 19 Feb 2014 12:07:43 GMT

Yes I have changed tat part already, i don't have fields such as period, i do have only date field, using tat date field only I will extract year, month, period, etc.,

Re: Re: Re: QVD Distribution according to Period

fernando_tonial — Wed, 19 Feb 2014 13:00:34 GMT

Ok, you try this SQL.

SQL Select * from YourTableName where Date >= '$(vDiaIni)' and Date <= '$(vDiaFim)'

Maybe you should change the date format, the format is, YYYYMMDD.

Best Regards.

Tonial.

Re: QVD Distribution according to Period

kalyandg — Thu, 20 Feb 2014 07:36:10 GMT

Hi all,

I had tried my level best, just by filtering date in the table.

I had created From and To notepad, in From, start date, in To end date,

Asked user to modify the date once they are updating db three months once.

I load tat notepad in QV, and set it as variable,

ex:

vFrom='$(Include.....);';

vTo='$(Include);';

Then in where condtion

where Date>=$(vFrom) and Date<=$(vTo)

and store it as

Table_$(vFrom)-$(vTo).qvd

then

I take all the qvd by using * sign after the table name.

EX;

Load * From Table_*.qvd;

so this will read all the data from the beginning.

Thanks to everyone who had replied to my discussion.

Best Regards,

Kalyan.D