topic Re: Storing different years data in yearly qvds in QlikView

Storing different years data in yearly qvds

Wed, 12 Nov 2014 11:17:45 GMT

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

Re: Storing different years data in yearly qvds

alexandros17 — Wed, 12 Nov 2014 11:26:02 GMT

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;

Let me know

Re: Storing different years data in yearly qvds

its_anandrjs — Wed, 12 Nov 2014 11:29:15 GMT

Hi,

Write store statements with load data with where condition

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

Re: Storing different years data in yearly qvds

MK_QSL — Wed, 12 Nov 2014 11:31:45 GMT

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_;

Drop Table Temp;

Re: Storing different years data in yearly qvds

jyothish8807 — Wed, 12 Nov 2014 11:31:58 GMT

Alessandro Saccone you are awesome.

Regards

Re: Storing different years data in yearly qvds

senpradip007 — Wed, 12 Nov 2014 11:32:36 GMT

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;

Re: Storing different years data in yearly qvds

alexandros17 — Wed, 12 Nov 2014 11:36:12 GMT

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 ...

Re: Storing different years data in yearly qvds

alexandros17 — Wed, 12 Nov 2014 11:37:13 GMT

Thanks ...

Re: Storing different years data in yearly qvds

jyothish8807 — Wed, 12 Nov 2014 11:38:41 GMT

Yes got it, Thanks for clearing doubts.

Regards

Re: Storing different years data in yearly qvds

Wed, 12 Nov 2014 11:39:07 GMT

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.

Re: Storing different years data in yearly qvds

jyothish8807 — Wed, 12 Nov 2014 11:41:46 GMT

Hi,

Try putting

where Year = $(i)

Regards

Re: Storing different years data in yearly qvds

its_anandrjs — Wed, 12 Nov 2014 12:04:40 GMT

Hi,

If want dynamic then write like this way with for loop

LOAD * INLINE [

Year, ID, Value

2012, A, 100

2012, B, 150

2013, A, 110

2013, B, 140

2014, C, 158

2015, D, 458

];

MinMax:

LOAD Min(Year) as YMin, Max(Year) as YMax Resident A;

LET vMinYear = Peek('YMin', 0, 'MinMax');

LET vMaxYear = Peek('YMax', 0, 'MinMax');

For i = vMinYear to vMaxYear

Temp:

NoConcatenate

load * Resident A where Year = $(i);

STORE * from Temp into Sample_$(i).qvd; //Sample_2013

Drop table Temp;

Regards

Anand