How to display minimum 5 years data in Qlik

[#1]

Dear Experts ,

In my report i want to display minimum 5 Years of records (2011-2016) in BI ,but in my case once financial year completed after 6 months we move all tables and record to History data source separately . Every year records and tables moved to separate data source .

I designed sales analysis report where my  links and table connections looks little complicated . Here i want to link past year data  .

For Ex , just imagine i have 20 QVD to design sales analysis report earlier, now i have 100 QVD (i,e for 5 years 20*5) like in a name of

(Transaction_Fact , Transaction_Fact_2014 , Transaction_Fact_2013 , Transaction_Fact_2012 , Transaction_Fact_2011) .

I want to concatenate with each QVD ? else is there any other alternate method to achieve this .

Please suggest .

[perumal_41]

Hi Sriram,

I assuming all table have same columns . Try below script in QV

LOAD * FROM Transaction_Fact.qvd (qvd) where years <= (year(today())-5);

[#3]

Hi ,

Thanks for your quick reply ,All tables having same column , but i have each year record in 5 different QVD . for ex (Transaction_Fact.qvd , Transaction_Fact_2014.qvd , Transaction_Fact_2013.qvd , Transaction_Fact_2012.qvd , Transaction_Fact_2011.qvd).

i want to concatenate with every qvd  separately ?

[amayuresh]

Option 1:

     Use complete transaction data and filter only 5 years of data using incremental load.

     Steps:

          1: Identify Max Date

          2: Store Max Data in vMax variable

          3: Pull latest New Five years of data using condition like

                    MonthStart(vMax - 5 Years) till MonthEnd(vMax)

          4: Load it into your Single QVD having 5 Years of data

Option 2:

     If your separate QVD format is same, then do concatenation to consolidate Five

[perumal_41]

Hi Sirram,

using star extract all wvd from source folder and where filter out 5 years record

LOAD * FROM Transaction_Fact*.qvd (qvd) where years <= (year(today())-5);

[Chanty4u]

try

Load Login_Date,User_ID,User_name

From (./Login.qvd)(qvd)

where  Login_Date <= Today() and Login_Date >= Addyears(Today(), -5);

[maxgro]

You can use wildcard in the load to read many qvd in a single (script) load

LOAD *

FROM [Transaction_Fact*.qvd] (qvd);

[MayilVahanan]

Hi

Try like this

LOAD * Where File > Year(Today())-5;

LOAD *, SubField(FileBaseName(),'_',-1) as File from Transaction_Fact_*.qvd(qvd);