topic Re: Need Last 3 years Data in QlikView

Need Last 3 years Data

SatyaPaleti — Sat, 16 Nov 2024 21:20:18 GMT

Hi Folks,

I have a requirement. In a folder there are qvd's from 2011 January to 2019 February. I need to fetch only last 3 years qvd's only. How can we do this. Could some one can help me on this

abc_201101.qvd

abc_201102.qvd

abc_201103.qvd

abc_201104.qvd

abc_201105.qvd

abc_201106.qvd

abc_201107.qvd

abc_201902.qvd

Thanks and Regards,

Satya

Re: Need Last 3 years Data

PrashantSangle — Wed, 27 Feb 2019 07:28:58 GMT

Hi,

Try below logic

Let vMaxYearMonth=num(Today());
Let vMinYearMonth=num(AddYears(Today(),-3));

TempCalendar:
LOAD
Date(AddMonths($(vMinYearMonth),IterNo() - 1),'YYYYMM') as TempDate
AutoGenerate 1
While AddMonths($(vMinYearMonth),IterNo()-1) <= $(vMaxYearMonth)
;

FOR i = 0 TO NoOfRows('TempCalendar') - 1
LET vYearMonth = peek('TempDate', $(i), 'TempCalendar');

Final:
Load *
FROM [lib://$(qvdPath)\abc_$(vYearMonth).qvd]
(qvd);

Next i;

Drop table TempCalendar;

Regards,
Prashant Sangle

Re: Need Last 3 years Data

jonathandienst — Wed, 27 Feb 2019 08:41:03 GMT

I use this code pattern:

Let vMinDate = AddYears(Today() - 3);

// Creating the start point means that we can force concatenate, even if the field list changes
Data:
LOAD '' as Source Autogenerate 0;

For Each vFile in FileList('*.qvd')  // change to correct path to qvd files
	Let vDate = Date#(TextBetween(vFile, 'abc_', '.qvd'), 'yyyyMM');
	If vDate >= vMinDate Then
	
		Concatenate(Data)
		LOAD *,
			FileBaseName() as Source
		FROM [$(vFile)];
		
	End If
Next

This code will not throw an error if any files are missing, and they will concatenate into a single table even if the field lists change over time. Also, the Source field will have the lineage for the data rows.

Re: Need Last 3 years Data

SatyaPaleti — Fri, 01 Mar 2019 07:36:11 GMT

Thank you so much Prashanth it works

@PrashantSangle wrote:
Hi,

Try below logic

Let vMaxYearMonth=num(Today());
Let vMinYearMonth=num(AddYears(Today(),-3));

TempCalendar:
LOAD
Date(AddMonths($(vMinYearMonth),IterNo() - 1),'YYYYMM') as TempDate
AutoGenerate 1
While AddMonths($(vMinYearMonth),IterNo()-1) <= $(vMaxYearMonth)
;

FOR i = 0 TO NoOfRows('TempCalendar') - 1
LET vYearMonth = peek('TempDate', $(i), 'TempCalendar');

Final:
Load *
FROM [lib://$(qvdPath)\abc_$(vYearMonth).qvd]
(qvd);

Next i;

Drop table TempCalendar;

Regards,
Prashant Sangle

Thank you Prashanth it works

Re: Need Last 3 years Data

SatyaPaleti — Fri, 01 Mar 2019 07:37:04 GMT

Thank you Jhonty. It works well