topic Re: Split data and Store to qvd in QlikView

Split data and Store to qvd

Anonymous — Wed, 30 Sep 2015 05:18:22 GMT

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

Re: Split data and Store to qvd

marcus_sommer — Wed, 30 Sep 2015 10:50:33 GMT

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

- Marcus

Re: Split data and Store to qvd

Gabriel — Wed, 30 Sep 2015 11:13:30 GMT

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;

Re: Split data and Store to qvd

Anonymous — Wed, 30 Sep 2015 12:10:11 GMT

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

Re: Split data and Store to qvd

Anonymous — Thu, 01 Oct 2015 02:04:52 GMT

Hi Guys,

Thanks for your response,

it solve my problem perfectly,

Thanks in advance

Re: Split data and Store to qvd

Gabriel — Thu, 01 Oct 2015 08:48:20 GMT

Hi,

Am glad that help

Re: Split data and Store to qvd

arixooo123 — Fri, 02 Jun 2017 06:25:29 GMT

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;