Storing different years data in yearly qvds

[#1]

I have the following data loaded into qlikview:

Year     ID     Value

2012     A     100

2012     B     150

2013     A     110

2013     B     140

I want to generate yearly QVDs based on the above data. e.g. in the above example there would be two QVDs generated Sample_2012.qvd and Sample_2013.qvd

[alexandros17 · Accepted solution]

Suppose your data are in the tabel MYTAB then:

temp:

Load Min(Year) as minY, max(Year) as maxY resident MYTAB;

LET MI = Peek('minY', 0, 'temp');

LET MA = Peek('maxY', 0, 'temp');

For i = $(MI) to $(MA)

     tabtemp:

     noconcatenate

     load * resident MYTAB where Year = $(MI);

     store * from tabtemp into myqvd_$(i).qvd;

     drop table tabtemp;

next

Let me know

[its_anandrjs]

Hi,

Write store statements with load data with where condition

A:

LOAD * INLINE [

    Year, ID, Value

    2012, A, 100

    2012, B, 150

    2013, A, 110

    2013, B, 140

];

NoConcatenate

2012:

LOAD

*

Resident A

Where Year = 2012;

STORE 2012 into QVD2012.qvd;

2013:

LOAD

*

Resident A

Where Year = 2013;

STORE 2012 into QVD2013.qvd;

DROP Table A;

Regards

Anand

[MK_QSL]

Sample:

Load * Inline

[

  Year, ID, Value

  2012,   A,  100

  2012,   B,  150

  2013,   A,  110

  2013,   B,  140

  2014, A, 400

  2014, B, 350

];

Temp:

Load Distinct Year as Temp_Year Resident Sample;

Let vCount = FieldValueCount('Temp_Year');

For i = 0 to vCount-1

  Let vYear = FieldValue('Temp_Year',$(i)+1);

  NoConcatenate

  Sample_:

  Load * Resident Sample Where Year = '$(vYear)';

  Store Sample_ into Sample_$(vYear).qvd(QVD);

  Drop Table Sample_;

Next

Drop Table Temp;

[jyothish8807]

Alessandro Saccone you are awesome.

Regards

KC

[senpradip007]

Range:

Load Min(Year) as FromYear,

max(Year) as ToYear resident MYTAB;

LET FromYear= Peek('FromYear', 0, 'temp');

LET ToYear = Peek('ToYear ', 0, 'temp');

For i = $(FromYear) to $(ToYear)

     Tab:

     noconcatenate

     load * From <source> where Year = $(FromYear);

     store * from Tab into qvd_$(i).qvd;

     drop table Tab;

Next

[alexandros17]

No $(i) because i is the index that starts from 2012 and loop through all the years ... you can combine string infact if you write xxx_$(i).qvd you will have files named: xxx_2012.qvd and so on ...

[alexandros17]

Thanks ...

[jyothish8807]

Yes got it,  Thanks for clearing doubts.

Regards

KC

[#10]

The above code is creating different yearly QVDs but the data is not getting stored properly in the QVD.

In all the QVDs, the Year value is coming as the min(Year) i.e. 2012.