How to split data into separate qvd after ever 250 record read ?

[shekhar_analyti]

Hi All ,

I have two scenarios of splitting data in separate qvd ;

1) Split data in separate qvd based on country . For example if i have total of 10 countries then , 10 qvds should be formed with data of respective countries only.

2) Irrespective of country , four set of qvd should be created with each having 250 records . Something like first 250 records in one qvd , next 250 in others .. **

I have attached sample data which is having 1000 records .

[sushil353]

Hi,

Hope below code will help you

Temp:

LOAD Company,

     Country,

     Sale_Date,

     Sale_Amount,

     Promo_Amount,

     Drug_Name

FROM

[Drug Sales.xlsx]

(ooxml, embedded labels, table is data);

let j=0;

let l=250;

for i=1 to Ceil(NoOfRows('Temp') / 250)

NoConcatenate

Temp2:

LOAD *

Resident Temp

where RecNo() > $(j) and RecNo()<= $(l);

STORE Temp2 into test$(i).qvd(qvd);

DROP Table Temp2;

let j=$(j)+250;

let l=$(l)+250;

NEXT i;

DROP Table Temp;

exit Script;

HTH

sushil

[shekhar_analyti]

Please attach qvw ..

[sushil353]

You can simply copy and paste this code in your qvw..

[shekhar_analyti]

Second scenario

[sushil353]

The code is for second scenario only..

Scenario 2

[shekhar_analyti]

I am really sorry .. I meant country wise split ... scenario 1

[sushil353 · Accepted solution]

For scenario 1:

use this code

Temp:

LOAD Max(1),

     Country

FROM

[Drug Sales.xlsx]

(ooxml, embedded labels, table is data)

Group by Country;

for i=0 to NoOfRows('Temp')-1

let vcountry = Peek('Country',$(i),'Temp');

Temp2:

LOAD *

FROM

[Drug Sales.xlsx]

(ooxml, embedded labels, table is data)

where Country = '$(vcountry)';

STORE Temp2 into test$(vcountry).qvd(qvd);

DROP Table Temp2;

NEXT i;

DROP Table Temp;

exit Script;

HTH

Sushil

[shekhar_analyti]

Thank you Sushil

[rahulpawarb]

Hello Shekhar,

You can refer the attached application along with QVDs.

Regards!

Rahul