Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a question,
i have a data like this.
I want to create QVD file for each YearMonth,
So it will create 14 Qvd file for each yearmonth.
201407.qvd
201408.qvd
201409.qvd
............... etc
Is it possible to make it ?
I also attach my qvw file here.
Really need your help, Thanks
Hi,
This should give you what you're after
Data:
LOAD * INLINE [
Name, Qty, TransactionDate
A, 1, 09/07/2014
B, 2, 09/08/2014
C, 3, 09/09/2014
D, 4, 09/10/2014
E, 5, 21/11/2014
F, 5, 27/12/2014
G, 5, 10/02/2015
H, 5, 12/03/2015
I, 5, 05/04/2015
J, 5, 05/05/2015
K, 6, 22/06/2015
L, 7, 01/07/2015
M, 8, 31/08/2015
];
YearData: // Loading the Month and Year to store
LOAD
DISTINCT
DATE(Date#(TransactionDate,'DD/MM/YYYY'),'MM-YYYY'') AS YrMonth // Formatting the Date
Resident Data;
FOR i = 0 to NoOfRows('YearData');
LET vYr_Split_StoreQVD = Peek('YrMonth',$(i),'YearData');
NoConcatenate
FinalTable:
LOAD *
Resident Data
WHERE
TransactionDate = '$(vYr_Split_StoreQVD)';
STORE FinalTable into FileDestination\Final_Table_$(vYr_Split_StoreQVD).QVD(QVD);
DROP Table FinalTable;
Next i;
DROP TABLES Data,YearData;
Have a look here: Year Wise Qvd creation from Source Data
- Marcus
Hi,
This should give you what you're after
Data:
LOAD * INLINE [
Name, Qty, TransactionDate
A, 1, 09/07/2014
B, 2, 09/08/2014
C, 3, 09/09/2014
D, 4, 09/10/2014
E, 5, 21/11/2014
F, 5, 27/12/2014
G, 5, 10/02/2015
H, 5, 12/03/2015
I, 5, 05/04/2015
J, 5, 05/05/2015
K, 6, 22/06/2015
L, 7, 01/07/2015
M, 8, 31/08/2015
];
YearData: // Loading the Month and Year to store
LOAD
DISTINCT
DATE(Date#(TransactionDate,'DD/MM/YYYY'),'MM-YYYY'') AS YrMonth // Formatting the Date
Resident Data;
FOR i = 0 to NoOfRows('YearData');
LET vYr_Split_StoreQVD = Peek('YrMonth',$(i),'YearData');
NoConcatenate
FinalTable:
LOAD *
Resident Data
WHERE
TransactionDate = '$(vYr_Split_StoreQVD)';
STORE FinalTable into FileDestination\Final_Table_$(vYr_Split_StoreQVD).QVD(QVD);
DROP Table FinalTable;
Next i;
DROP TABLES Data,YearData;
Great code Gabriel Oluwaseye
Here is another approach without using the peek.
It should give the same result.
-----------------------------------------
let cnt=FieldValueCount('YrMonth');
FOR i =1 to cnt;
LET vYr_Split_StoreQVD = FieldValue('YrMonth',i);
Hi Guys,
Thanks for your response,
it solve my problem perfectly,
Thanks in advance
Hi,
Am glad that help
Hi Gabriel,
I am trying your script, It created the first two qvds, but it seems that it fell into a loop where repeating the process all over again and again. I had to stop the script after 8 minutes and creating only 2 QVDs.
Would you please take a look at below scripts :
Data:
LOAD *;
SQL SELECT *
FROM ChainIT.dbo.Mytable
where Created>= '2016-07-01';
YearData:
LOAD
DISTINCT Date(Floor(Created),'YYYY-MM') AS YrMonth // Formatting the Date
Resident Mytable;
FOR i = 0 to NoOfRows('YearData')-1;
LET vYrMonth = Peek('YrMonth',$(i),'YearData');
NoConcatenate
FinalTable:
LOAD *
Resident Mytable
WHERE Date(Floor(MyDateColumn),'YYYY-MM') = '$(vYrMonth)';
STORE FinalTable into '$(vPathStore)\MyTable_$(vYrMonth).QVD'(QVD);
DROP Table FinalTable;
next i;
Drop table Mytable,YearData;