splitting single QVD data into 2 QVD's

[Anonymous]

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

[Peter_Cammaert]

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

1. Load Current.QVD and History.QVD (if they are available) into the same internal table.
2. 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)
3. Save all records that are not older than AddMonths(MonthStart(Today()), -2) to Current.QVD, thereby overwriting the file.
4. 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

[sasikanth]

hi

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

[Anonymous]

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

[Peter_Cammaert]

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

[tamilarasu]

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.

[Anonymous]

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

[tamilarasu]

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.