how to create snapshot of current week data as qvd dynamically

[bushpalaamarnat]

Hi all,

Could you help, every week we get data in the following format.

Weeks	Material code	Location
W18-18	ABC	Ind
W18-18	BCD	USA
W18-18	ABC	UK
W19-18	BCD	Ind
W20-18	DCE	UK
W20-18	ABC	UK

for which the first week we call it as current week, for example the above table has W18-18 as the week which is current week for this year. Now every week i need to save a snap shot of the current week data as a qvd for next week reference.

Like that i need to create 6 week data which will become a 6 week history.

Could you please advise on how to write a back end  to store data.

[Miguel_Angel_Baeyens]

Key here is how to create the field for the week as you have it in your data model.

Without testing, looks like it could be retrieved during the load script from any timestamp using

'W' & Week(DateField) & '-' & Right(Year(DateField), 2) AS Weeks

Once you have it, you can do a loop for each possible value and store every week in a different QVD:

AllWeeks:

LOAD Concat(DISTINCT Chr(39) & Weeks & Chr(39), ',') AS AllWeeks

FROM Table.qvd (qvd); // whatever the source for the Weeks field above mentioned

LET vAllWeeks = FieldValue('AllWeeks', 1);

For Each vWeek In vAllWeeks // check here the $() if required

Table:

LOAD *

FROM Source

WHERE Weeks = vWeek; // again, check for $()

STORE Table INTO [Data_$(vWeek).qvd] (qvd);

DROP Table;

Next

[YoussefBelloum]

Hi,

it depends on your needs..

you can store the entire table each week (including the current week and the previous weeks) and store it with actual week number as indication.

just use the STORE command with this syntax:

LET vStore= Week(Today());

Store TABLE_NAME Into [your_path\TABLE_NAME_$(vStore).qvd];

[bushpalaamarnat]

yes,  you correct - "you can store the entire table each week (including the current week and the previous weeks) and store it with actual week number as indication.

This is my need.

what is the best way to do this sir.

[bushpalaamarnat]

Sir, the logic which you specified is the absolute one. Thank you!

'W' & Week(DateField) & '-' & Right(Year(DateField), 2) AS Weeks