topic How to split data Row number wise in QlikView

How to split data Row number wise

pra_kale — Thu, 19 May 2016 16:33:01 GMT

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.

Re: How to split data Row number wise

sunny_talwar — Thu, 19 May 2016 16:42:31 GMT

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

Re: How to split data Row number wise

trdandamudi — Thu, 19 May 2016 16:47:04 GMT

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 ?

Re: How to split data Row number wise

Clever_Anjos — Thu, 19 May 2016 16:49:50 GMT

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;

Re: How to split data Row number wise

settu_periasamy — Thu, 19 May 2016 16:49:54 GMT

May be check this

Paging Implementation for Straight table

Re: How to split data Row number wise

pra_kale — Thu, 19 May 2016 18:01:56 GMT

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.

Re: How to split data Row number wise

Kushal_Chawda — Thu, 19 May 2016 18:16:36 GMT

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

Re: How to split data Row number wise

Clever_Anjos — Thu, 19 May 2016 18:28:08 GMT

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

Re: How to split data Row number wise

pra_kale — Fri, 20 May 2016 01:46:02 GMT

Ok. Once again Thanks for Help...

Re: How to split data Row number wise

lakshmanvepuri — Sun, 19 Apr 2020 16:26:19 GMT

Thanks a lot for the Solution...

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