Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

How to split data Row number wise

Hi,

I have attached one sample application in which i have imported one excel file.

If i want to split this data on the basis of number of rows like by keeping a limit of 250 in a one file. Means 1 file is get saved of 250 and another of 250 and so on.

Can you please help me how i can do this.

Thanks in advance.

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: How to split data Row number wise

Maybe this

A:

LOAD RowNo() as row,

PolicyNo

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET file=0;

LET count=NoOfRows('A');

DO WHILE count > 0

  t:

  FIRST (250)

  LOAD

  PolicyNo

  resident A

  where row > $(file)*250 and row < ($(file)+1)*250;

  store t into f$(file).txt(txt);

  drop Table t;

  LET count = $(count) - 250;

  LET file = $(file) + 1;

LOOP;

View solution in original post

9 Replies
Highlighted

Re: How to split data Row number wise

You mean 150 rows from one file? Not sure I understand your question

Highlighted
Master II
Master II

Re: How to split data Row number wise

Are you trying to load the excel file and then splitting the data every 250 rows and saving back to Excel ? Is that what you want ?

Highlighted
Employee
Employee

Re: How to split data Row number wise

Maybe this

A:

LOAD RowNo() as row,

PolicyNo

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET file=0;

LET count=NoOfRows('A');

DO WHILE count > 0

  t:

  FIRST (250)

  LOAD

  PolicyNo

  resident A

  where row > $(file)*250 and row < ($(file)+1)*250;

  store t into f$(file).txt(txt);

  drop Table t;

  LET count = $(count) - 250;

  LET file = $(file) + 1;

LOOP;

View solution in original post

Highlighted

Re: How to split data Row number wise

Highlighted
Creator III
Creator III

Re: How to split data Row number wise

Hi All,

Thanks all for your help!!

In real scenario i am having a data more than 15 to 20 lacs, it may increase also, as i want to share data in to .xlsx file i required to split the data into 5 lacs records. E.g. If my records are 20 lacs then i will get the the 5 files containing 5 lacs records in each file.

Thanks Anjos.. code is working perfectly. I have made only one change instead of .text i have put .csv and it is working perfectly. But if i want to save the file into .xlsx whether it is possible.

Highlighted
MVP
MVP

Re: How to split data Row number wise

No you cannot store in xlsx. You can only store it in QVD, CSV or TXT

Highlighted
Employee
Employee

Re: How to split data Row number wise

Unfortunately exporting to xls(x) is not a native feature

Highlighted
Creator III
Creator III

Re: How to split data Row number wise

Ok. Once again Thanks for Help...

Highlighted
Creator
Creator

Re: How to split data Row number wise

Thanks a lot for the Solution...

 

I am checking for the sample for the same and found the solution is clear.