topic Re: How to split data into separate qvd after ever 250 record read ? in QlikView

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

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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 .

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

sushil353 — Thu, 16 Mar 2017 04:41:22 GMT

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

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

shekhar_analyti — Thu, 16 Mar 2017 04:47:14 GMT

Please attach qvw ..

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

sushil353 — Thu, 16 Mar 2017 04:50:07 GMT

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

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

shekhar_analyti — Thu, 16 Mar 2017 04:53:59 GMT

Second scenario

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

sushil353 — Thu, 16 Mar 2017 04:59:52 GMT

The code is for second scenario only..

Scenario 2

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

shekhar_analyti — Thu, 16 Mar 2017 05:04:30 GMT

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

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

sushil353 — Thu, 16 Mar 2017 05:04:50 GMT

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

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

shekhar_analyti — Thu, 16 Mar 2017 05:06:27 GMT

Thank you Sushil

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

rahulpawarb — Thu, 16 Mar 2017 05:30:15 GMT

Hello Shekhar,

You can refer the attached application along with QVDs.

Regards!

Rahul

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

shekhar_analyti — Thu, 16 Mar 2017 08:15:14 GMT

Hi Sushil ,

Above script helped me for my requirement .

But Please help me understand that why you have used

Temp:

LOAD Max(1),

Country

FROM

[Drug Sales.xlsx]

(ooxml, embedded labels, table is data)

Group by Country;

Why not only below script .

------------------------------------Script2--------------------------

Temp:

LOAD

Country

FROM

[Drug Sales.xlsx]

(ooxml, embedded labels, table is data)

Group by Country;

I tried executing script 2 but its giving error : Aggregation expressions required by GROUP BY clause