Create Monthly Snapshot qvd

[Ramsingh]

Hi All ,

How do i create Monthly Snapshot .

Example : I have Product ID in Table ( only 1 field ) and when i refresh for the first time ie Today , i want new field to be created telling the Refresh time ( Year and Month ) and store it to qvd with Name the qvd as "Product 2020 nov" and when refresh the source again next time i want the new Products ( 4, 5 , 6 ) to be tagged as refresh time ( next month ) - 12/4/2020 and again store the qvd telling "Product 2020 Dec" and so on every Month.

Hope I'm clear

Product ID      OutPut Field Required

1                         11/4/2020

2                           11/4/2020

3                          11/4/2020

4                          12/4/2020

5                         12/4/2020

6                            12/4/2020

Advance Thanks

[edwin]

there are two components to this:  

1. a complete list of what has been loaded
2. monthly QVDs for new ones loaded for the month

the logic is load everything from the complete list QVD, get a list of new products and stamp with today's date
then check if the current month qvd exists: store else load it, add the new ones, store
of course update the complete list with the new ones

this is the quick and dirty just to give you an idea and im sure it can be optimized

Prior to the run:

this is after adding H, I to the inline:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LoadedProducts:
//this is for first time run
//load * inline [
//Product, Date
//A, 10/1/2020
//B, 10/1/2020
//];
load * from AllProducts.qvd (qvd);

NoConcatenate
Products:
load NewProduct, date(Today(),'M/D/YYYY') as LoadDate
where not exists(Product, NewProduct)
;
load * inline [
NewProduct
A
B
C
D
E
F
G
];


concatenate(LoadedProducts)
load NewProduct as Product, LoadDate as Date Resident Products;


STore LoadedProducts into AllProducts.qvd;

let vThisMonth=date(Today(),'MMM-YYYY');

LET vListqvdexists=isnull(QvdCreateTime('$(vThisMonth).qvd'));

if $(vListqvdexists)=-1 then
	store Products into [$(vThisMonth).qvd];
ELSE
	NoConcatenate
	tmpProducts:
	load NewProduct as tempProduct, date(LoadDate,'M/D/YYYY') as LoadDate from [$(vThisMonth).qvd] (qvd);
	
	Concatenate (tmpProducts)
	load NewProduct as tempProduct, date(LoadDate,'M/D/YYYY') as LoadDate Resident Products where not exists(tempProduct, NewProduct);
	
	NoConcatenate
	ToSaveProducts:
	load tempProduct as NewProduct, date(LoadDate,'M/D/YYYY') as LoadDate Resident tmpProducts;
	
	store ToSaveProducts into [$(vThisMonth).qvd];
	
	drop tables tmpProducts, ToSaveProducts;
ENDIF

[Ramsingh]

Hi Edwin , 

Thanks for replying .

Can I make it simpler .

How can I achieve this ? 

Basically instead of having qvd for each time I refresh .

Can I have one qvd only with refresh date as new field .

Example

ID   date 

1.   If I refresh today , I shud get today date 

2.  If I refresh today , I shud get today date 

Let's say tomor I have any new data in the table so when I refresh , for those new data I shud have refresh date as today date's stored in same qvd ( with existing date - yesterday s data ) 

Hope iam clear

[MayilVahanan]

Hi @Ramsingh 

After the first load, try like below

let vQvdAvailable = not Isnull(QvdCreateTime('Products.qvd'));

If $(vQvdAvailable) =-1 then

NoConcatenate
FinalProd:
LOAD * From Products.qvd(qvd);

Concatenate
Product:
Load *, Date(Today()+15) as LoadDate Inline
[
ProductID
1
2
3
4
5
6
] Where not Exists(ProductID);

Store FinalProd into Products.qvd(qvd);

DROP Table FinalProd;

ENDIF