Split data and Store to qvd

[Anonymous]

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

[marcus_sommer]

Have a look here: Year Wise Qvd creation from Source Data

- Marcus

[Gabriel · Accepted solution]

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;

[Anonymous]

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);

[Anonymous]

Hi Guys,

Thanks for your response,

it solve my problem perfectly,

Thanks in advance

[Gabriel]

Hi,

Am glad that help

[arixooo123]

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;