topic Splitting files in QlikView

Splitting files

Thu, 20 Aug 2015 15:19:04 GMT

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

Re: Splitting files

Thu, 20 Aug 2015 15:22:14 GMT

You can do. What is the purpose of spiting the files ?

In Qlikview 8764 rows is not a bad.

Re: Splitting files

Thu, 20 Aug 2015 15:27:06 GMT

hi dathu,

the requirement is like that.

Each file should have only 900 lines and the file has to be sent thro NPrinting.

pls help me to do this

Re: Splitting files

Anonymous — Thu, 20 Aug 2015 15:28:32 GMT

hi,

Please try to use filters in calculated dimension or expressions to split records as per your requirement.

Regards

Neetha

Re: Splitting files

Thu, 20 Aug 2015 15:31:44 GMT

is it possible in the script?

i cant use calculated dimensions.

i need to split from the table

Re: Splitting files

Anonymous — Thu, 20 Aug 2015 15:38:07 GMT

Please Check if this helps:

How to Create Reports Based on QlikView Entities – Customer Feedback for Vizubi

Re: Splitting files

Thu, 20 Aug 2015 15:43:10 GMT

thanks for the response.

but give some suggestions to do in script itself.

is it possible to split one table to several tables with 900 lines.

i.e., my table has 8764 lines, then i need to split into 10 files.

is it possible?

by using filter like rowno?

pls help

Re: Splitting files

Thu, 20 Aug 2015 15:53:14 GMT

Do you have only one table in your data model ?

Is pivot table creating from one table fields ?

Add the Newfield to your dimension table have 8374 records called Bucket with below expression:

Ceil(RowNo()/900) AS Bucket.

So Bucket have 1 , 2 3, etc for each 900 rows in the table. So you can simply create your own no of Pivvot tables based on that.

Re: Splitting files

jagan — Thu, 20 Aug 2015 16:02:08 GMT

Hi,

In script try like this

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;

After executing this script, a separate QVD is created for every 900 rows in your data.

Hope this helps you.

Regards,

Jagan.

Re: Splitting files

Anonymous — Thu, 20 Aug 2015 16:09:37 GMT

hi,

instead of splitting data for 900 records each ,split by months or years and store in qvds.

this way maintenance wise its easier.

Regards

Neetha

Re: Splitting files

Thu, 20 Aug 2015 16:27:12 GMT

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

Re: Splitting files

Thu, 20 Aug 2015 16:33:28 GMT

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

Re: Splitting files

maxgro — Thu, 20 Aug 2015 17:35:05 GMT

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;

Re: Splitting files

jagan — Fri, 21 Aug 2015 01:49:25 GMT

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;

Re: Splitting files

Fri, 21 Aug 2015 08:14:30 GMT

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

Re: Splitting files

jagan — Fri, 21 Aug 2015 08:17:44 GMT

Hi,

Is the script in my previous post is working?

Regards,

jagan.

Re: Splitting files

Fri, 21 Aug 2015 08:23:42 GMT

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

Re: Splitting files

jagan — Fri, 21 Aug 2015 08:34:24 GMT

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;

Re: Splitting files

Fri, 21 Aug 2015 08:50:54 GMT

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 ?

Re: Splitting files

jagan — Fri, 21 Aug 2015 09:03:20 GMT

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;