Splitting files

[#1]

Hi Experts,

I have a pivot table with 8764 lines of records.

I need to split into sub files with 900 lines of records each.

Is it possible?

pls give suggestions

[#2]

jagan,

thanks for the script.

when i use your script, it gives 9 qvds. (but i have 8764 lines. i.e., 8764/900=9.73 means around 10 qvds)

in that 9 qvds, first two qvds have only 9 lines.

qvd1 = row 1 to row9

qvd2 = row11 to row19

qvd3=0

.

.

.

qvd9=0

why is it so.

also 10th row, 20th rows are missing.

how should i correct the script. pls help

[#3]

dathu,

i have only one table and the pivot table is from one table fields.

i tried Ceil(RowNo()/900) AS Bucket.

it is adding a new field Bucket to the table.

when i select Bucket 1, it is giving first 900 lines in table, bucket 2-next 900 lines, etc.

how can i design no. of pivot tables with this Bucket?

pls give suggestions

[maxgro]

try this

// test data

Data:

LOAD

RowNo() AS RowNum

AutoGenerate 8764;

LET vRowCount =NoOfRows('Data');

LET vNoOfPages = Ceil((vRowCount)/900);

LET vRowStartNum = 1;

trace vRowCount=$(vRowCount);

trace vNoOfPages=$(vNoOfPages);

For Index = 1 to vNoOfPages

  LET vRowEndNum = Index * 900;

  trace vRowStartNum=$(vRowStartNum);

  trace vRowEndNum=$(vRowEndNum);

  Temp:

  NoConcatenate

  LOAD

  *

  RESIDENT Data

  WHERE RowNum >= $(vRowStartNum) AND RowNum <=$(vRowEndNum);

  STORE Temp INTO File$(Index).qvd;

  DROP TABLE Temp;

  LET vRowStartNum = vRowEndNum +1;

NEXT Index;

[jagan]

Hi Rajendran,

There is typo mistake in the script, replace below line

LET vNoOfPages = Ceil(vRowCount/900);

Now the latest script is

Data:

LOAD

RowNo() AS RowNum,

*

FROM DataSource;

LET vRowCount =NoOfRows('Data');

LET vNoOfPages = Ceil(vRowCount/900);

LET vRowStartNum = 1;

For Index = 1 to vNoOfPages

LET vRowEndNum = vNoOfPages * Index;

Temp:

NoConcatenate

LOAD

*

RESIDENT Data

WHERE RowNum >= $(vRowStartNum) AND RowNum <=$(vRowEndNum);

STORE Temp INTO File$(Index).qvd;

DROP TABLE Temp;

LET vRowStartNum = vRowStartNum  + vRowEndNum ;

NEXT Index;

[#6]

Hi Massimo,

thanks for the script.

you have mentioned

Data:

LOAD

RowNo() AS RowNum

AutoGenerate 8764;

Actual file is uploaded from directory and the content varies daily.

No.of lines may increase from 8764 or may decrease.

Can we set it automatic?

If so, how can i do that. pls help

[jagan]

Hi,

Is the script in my previous post is working?

Regards,

jagan.

[#8]

jagan,

i tried this too,

again same problem.

now its fetching 10 rows in each qvd. total 9 qvds.

i need to get 10 qvds with 900 lines each.

pls help

[jagan]

Hi,

Try this script

Data:

LOAD

RowNo() AS RowNum,

*

FROM DataSource;

LET vRowsPerFile = 900;

LET vRowCount =NoOfRows('Data');

LET vNoOfPages = Ceil(vRowCount/vRowsPerFile);

LET vRowStartNum = 1;

For Index = 1 to vNoOfPages

LET vRowEndNum = vRowsPerFile * Index;

Temp:

NoConcatenate

LOAD

*

RESIDENT Data

WHERE RowNum >= $(vRowStartNum) AND RowNum <=$(vRowEndNum);

STORE Temp INTO File$(Index).qvd;

DROP TABLE Temp;

LET vRowStartNum = vRowStartNum  + vRowEndNum ;

NEXT Index;

[#10]

Jagan,

now i m getting 10 qvds.

qvd1 has 900lines and qvd2 has next 900.

but all other qvds has got 0 lines.

why is it so,

how should i correct ?

[jagan · Accepted solution]

Hi Rajendran,

Check below script, it is working

Data:

LOAD

RowNo() AS RowNum,

*

FROM DataSource;

LET vRowsPerFile = 900;

LET vRowCount =NoOfRows('Data');

LET vNoOfPages = Ceil(vRowCount/vRowsPerFile);

LET vRowStartNum = 1;

For Index = 1 to vNoOfPages

LET vRowEndNum = vRowsPerFile * Index;

Temp:

NoConcatenate

LOAD

*

RESIDENT Data

WHERE RowNum >= $(vRowStartNum) AND RowNum <=$(vRowEndNum);

STORE Temp INTO File$(Index).qvd;

DROP TABLE Temp;

LET vRowStartNum = vRowEndNum + 1;

NEXT Index;