topic Add Data with same fields when excel rows limit(10 Million rows) expires in App Development

Add Data with same fields when excel rows limit(10 Million rows) expires

amanjain57 — Mon, 03 Apr 2017 13:12:03 GMT

Hi,

I am manually uploading the excel files in the QlikSense script to update the data in my QlikSense app.

One of the excel files has reached its maximum rows: 10 million rows beyond which no data could be added.

Still i want to add data to this table and show it up in qlik sense app.

Example : A table created out of my excel file has data from January to March month 2017 with 10 M rows.

Now I have data from April 2017 which i could not upload on the existing excel file as the data rows have reached the limit.

So, I created a new excel file with similar fields(column values) but only difference is the data is added from April month.

How can i combine these two excel files into into one in QlikSense script so that the rows from new excel file adds to rows in old excel file ???

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

hector_munoz — Mon, 03 Apr 2017 13:19:04 GMT

Hi Aman,

You can create a variable with a mask and use it on FROM sentence:

SET vsFile = 'SALES_????_??_??.xlsx';

LOAD *

FROM $(vsFile) (file_parameters) ;

Hope it serves!

Regards,

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

amanjain57 — Mon, 03 Apr 2017 13:25:07 GMT

Hi Munoz,

But I have two files.

File 1 - data from January- March

File 2- April and beyond

1. Can you explain what do you mean by mask and why do we need to do that?

2. When Loading you are loading only the (vsFile), what about the other file i need to ??

So basically how are we combining the tables so that first all the rows from File 1 appears with same columns and then all the rows from File 2 appears with same columns.

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

hector_munoz — Mon, 03 Apr 2017 13:30:50 GMT

Hi Aman,

Then you would have to use an alternative way to name the files: eg SALES_2017_01.xlsx (january-march), SALES_2017_02.xlsx (for april and may), ...

Regards,

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

aarkay29 — Mon, 03 Apr 2017 13:33:08 GMT

you can try like this;

Load *

From File1;

Concatenate

Load *

From File 2;

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

amanjain57 — Mon, 03 Apr 2017 13:33:49 GMT

Yes, that's understood.

But how do i combine this two files ??

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

amanjain57 — Mon, 03 Apr 2017 13:35:17 GMT

Hi Aar Kay,

This seems easy, I will try out this.

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

hector_munoz — Mon, 03 Apr 2017 13:57:18 GMT

Hi,

What Aar Kay proposes is a solution but ou have to touch script every time a new file exists... I attach you a sample with the solution I proposed... Another solutiong would pass using FOR EACH loop...

Regards,

Re: Add Data with same fields when excel rows limit(10 Million rows) expires

amanjain57 — Mon, 03 Apr 2017 14:02:56 GMT

Thanks Munoz,

I will try both of the suggestions and let you and Aar Kay know the results I am getting

You are right that i need to concatenate every time their is a new file.

Probably a for loop or anything else would work better for future.