topic Re: Help with simple append load in QlikView

Help with simple append load

Fri, 29 Jan 2016 13:35:08 GMT

Hi,

I have a qvd file that is updated daily that show in-transit inventory.

Now I would like to save a snapshot of the in-transit inventory every last day of a month so that I can use this to show monthly trends going forward.

Basicly what I have done is to create a field called CheckMonthEnd with this logic: If(Date(Now())=MonthEnd(Date(Now())),1,0) as CheckMonthEnd. CheckMonthEnd will have a value 1 or 0.

What I want to do now is to save the file with a date appended to the file name if CheckMonthEnd=1.

Attached is the qvd. Hope somebody could point me in the right direction.

Best regards,
Freddy

Re: Help with simple append load

prieper — Wed, 03 Feb 2016 13:55:10 GMT

Think that you only need to advise the content of the field MaxCheckMonthEnd to the variable?

If so, please change your line 80 to:

let vCheckMonthEnd = PEEK('MaxCheckMonthEnd', 0, 'MaxMonth');

HTH Peter

Re: Help with simple append load

Peter_Cammaert — Wed, 03 Feb 2016 14:50:28 GMT

In your script, you could do a test for the end of the month (if we assume that your script runs 7 days a week), and store the resident table a second time to a different file. Like

STORE InTransitInventory INTO [IntransitInventory.qvd] (qvd); // Regular daily store

IF Floor(Today()) = Floor(MonthEnd(Today())) THEN

LET vFileTag = date(Today(), 'YYYYMMDD');

STORE InstransitInventory INTO [IntransitInventory_$(vFileTag).QVD] (qvd); // Special store

END IF

Best,

Peter

Re: Help with simple append load

Wed, 10 Feb 2016 12:11:30 GMT

I implemented following solution:

let vSaveDate = date(today(), ‘YYYYMMDD’);

let vPeriod = MonthName(Now());

let vCheckMonthEnd = If(Date(floor(Now()))=Date(Floor(MonthEnd(Now()))),1,0);

STORE InTransit INTO \\InTransit_Daily_$(vSaveDate).qvd;

if $(vCheckMonthEnd) = 1 then

STORE InTransit INTO \\InTransit_Monthly_$(vPeriod).qvd;

end if