topic Re: splitting single QVD data into 2 QVD's in QlikView

splitting single QVD data into 2 QVD's

Anonymous — Wed, 30 Dec 2015 08:49:41 GMT

Hello Everyone,

I am new to Qlikview and have requirement like below.

I have a one file and it is containing 3 months of data say May,June,July. I have to create QVD name as last3months.QVD based on incremental load. I am fine up to here. Suppose if data comes in August I have to show only June,July and August data in last3months.QVD. That May month data should be go to History.QVD. In same way I have to see only latest last 3 months data in last3months.QVD and rest of data in History.QVD.

Please see the below example.

Current.QVD History.QVD

June May

July

August

Current.QVD History.QVD

July May

Aug June

September

Current.QVD History.QVD

August May

September June

October July

Like wise i have to achieve.

Please help me how to achieve this requirement. Thanks in advance.

Regards,

Kumar

Re: splitting single QVD data into 2 QVD's

Peter_Cammaert — Wed, 30 Dec 2015 09:14:52 GMT

Juist a guideline that may help you start thinking about an approach. Try to keep it as simple as possible.

Load Current.QVD and History.QVD (if they are available) into the same internal table.
From your data source (possibly a DBMS), add all records that are newer than the most recent one in your internal table. If the internal table doesn't exist yet, or it doesn't contain any records (use NoOfRows() to check for this situation), set the starting date to a long time ago (in a galaxy far away)
Save all records that are not older than AddMonths(MonthStart(Today()), -2) to Current.QVD, thereby overwriting the file.
Likewise, save all records that are older than AddMonths(MonthStart(Today()), -2) to History.QVD.

This approach will allow you to start with nothing (no Current.QVD or History.QVD), and covers interruptions (you forget to reload for one or more months) in an elegant way.

Best,

Peter

Re: splitting single QVD data into 2 QVD's

sasikanth — Wed, 30 Dec 2015 09:15:38 GMT

while loading it self use filters ,

Last3months:

Load * from Table where Year<=max(Year) and year>=max(Year)-2

STORE Last3months into 😕Last3months.qvd(qvd);

History:

Load * from Table where year<max(Year)-2;

STORE History into 😕History.qvd(qvd);

try once

Re: splitting single QVD data into 2 QVD's

Anonymous — Wed, 30 Dec 2015 09:25:15 GMT

Hi Peter,

Thanks for ur reply.

we are getting files on daily basis and this will be overwrite on daily basis. This is a fact table.

This is how my code looks like.

Can you suggest me where do I include these functionality.

Inserts:

LOAD *

FROM

$(Vqvxpath)fact_site_visit_inserts.qvx(qvx);

STORE Inserts into $(Vqvdpath)fact_site_visit.QVD(qvd);

DROP Table Inserts;

Regards,

Kumar

Re: splitting single QVD data into 2 QVD's

Peter_Cammaert — Wed, 30 Dec 2015 09:38:41 GMT

Actually this LOAD is step 2 in my list. You will still have to add a where clause that only imports records that are newer than what you already have. In that way, you can avoid loading the same data twice because you executed the reload script twice on the same day.

I'm sorry to hear that you won't have any historical data from before you start reloading your document-to-be.

Peter

Re: splitting single QVD data into 2 QVD's

tamilarasu — Wed, 30 Dec 2015 10:25:12 GMT

Hi,

You can try something like below,

Test:
LOAD Max(Month) as MaxMonth
FROM
Input.qvd (qvd);

Let MaxMonth = Date(Addmonths(Peek('MaxMonth',0,'Test'),-1),'MMM YYYY');
Let MinMonth = Date(Addmonths(Peek('MaxMonth',0,'Test'),-3),'MMM YYYY');

Drop Table Test;


Current:
LOAD*
FROM Input.qvd (qvd) Where Month >= '$(MaxMonth)' and Month <='$(MinMonth)';

Store Current into Current.qvd(qvd);
Drop Table Current;

History:
LOAD *
FROM Input.qvd (qvd) Where Month < '$(MinMonth)';

Store History into History.qvd(qvd);
Drop Table History;

I just assumed the Input file date format as "MMM YYYY'. You can alter the code according to your requirement.

Note: I have not tested the script. Let me know.

Re: splitting single QVD data into 2 QVD's

Anonymous — Wed, 30 Dec 2015 11:33:03 GMT

Hi Nagaraj,

Thanks. I have only created date column in DD-MM-YYYY HH:MM:SS format and this is coming from fact table.

Regards,

Kumar

Re: splitting single QVD data into 2 QVD's

tamilarasu — Wed, 30 Dec 2015 12:04:26 GMT

Hi Kumar,

Try,

Test:

LOAD *,

Max(Date(Floor(Timestamp#(Fieldname)),'DD/MM/YYYY')) AS as MaxMonth

FROM

Input.qvd (qvd);

Let MaxMonth = Date(MonthEnd(Addmonths(Peek('MaxMonth',0,'Test'),-1)),'DD/MM/YYYY hh:mm:ss');

Let MinMonth = Date(MonthStart(Addmonths(Peek('MaxMonth',0,'Test'),-3)),'DD/MM/YYYY hh:mm:ss');

Drop Table Test;

.......

Rest of the script same.

Use debug mode to check the MaxMonth and MinMonth values. Let me know.