Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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];
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.
Sir, the logic which you specified is the absolute one. Thank you!
'W' & Week(DateField) & '-' & Right(Year(DateField), 2) AS Weeks