topic Re: Create Monthly Snapshot qvd in QlikView

Create Monthly Snapshot qvd

Ramsingh — Wed, 04 Nov 2020 11:29:57 GMT

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

Re: Create Monthly Snapshot qvd

edwin — Thu, 05 Nov 2020 21:29:43 GMT

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

Re: Create Monthly Snapshot qvd

Ramsingh — Wed, 11 Nov 2020 04:21:12 GMT

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

Re: Create Monthly Snapshot qvd

MayilVahanan — Wed, 11 Nov 2020 06:20:59 GMT

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