Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Split data and Store to qvd

Hi,

I have a question,

i have a data like this.

data.png

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

1 Solution

Accepted Solutions
Gabriel
Partner - Specialist III
Partner - Specialist III

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;

View solution in original post

6 Replies
marcus_sommer

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

- Marcus

Gabriel
Partner - Specialist III
Partner - Specialist III

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
Not applicable
Author

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
Not applicable
Author

Hi Guys,

Thanks for your response,

it solve my problem perfectly,

Thanks in advance

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

Am glad that help

arixooo123
Creator III
Creator III

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;