topic Re: Extracting data from Excel in QlikView

Extracting data from Excel

madhumitha — Thu, 02 Nov 2017 12:21:53 GMT

Hello All,

Below is the format of my source excel. I am trying to extract the break down fields (both Alphabet and the numbers) as a different table in my script. How should I approach it?

Primary Doc no	ADXXXXXX
Primary Date Start	DD-MM-YYY
Primary Date End	DD-MM-YYY
Primary Reference	125
Secondary Doc no	ADXXXXXX
Secondary Date Start	DD-MM-YYY
Secondary Date End	DD-MM-YYY
Secondary Reference	125
Completeness	% Complete
Some long Text	XX%




	Break Down
	ABC	15
	QEF	12
	REF	3
	GAR	2

Re: Extracting data from Excel

Ralf_Heukäufer — Thu, 02 Nov 2017 12:45:16 GMT

Hello,

can you upload an example Excel file?

Re: Extracting data from Excel

madhumitha — Thu, 02 Nov 2017 12:51:59 GMT

Hi ,

I have attached the excel.

I am trying to extract E-Doc Name and No of Queries into one table in QV.

Thanks in Advance

Re: Extracting data from Excel

Ralf_Heukäufer — Thu, 02 Nov 2017 12:58:40 GMT

For that you can use:

LOAD

"E-Doc name",

"NO of Queries"

FROM [lib://Path to your File/Sample.xlsx]

(ooxml, embedded labels, header is 20 lines, table is Sheet1);

header is 20 lines says here he begins to read after line 20 as header

Re: Extracting data from Excel

madhumitha — Thu, 02 Nov 2017 13:07:00 GMT

Thanks Ralf.

It extracts data.

Is there anyway I could stop the extraction when QV finds a newline in the excel?

Re: Extracting data from Excel

Ralf_Heukäufer — Thu, 02 Nov 2017 13:14:36 GMT

I think not directly,

you can save the number of lines to a variable and work with an if clause in the next read like:

if (rowno() > myvaraible, exit script)

or you can read a fixed number of lines with the first command:

First ‒ Qlik Sense

Re: Extracting data from Excel

Ralf_Heukäufer — Thu, 02 Nov 2017 14:39:52 GMT

Is that what you wanted to know, or did I misunderstand your second question?

If the answer was correct please mark your Your Question as correct.

Re: Extracting data from Excel

madhumitha — Fri, 03 Nov 2017 02:27:32 GMT

Sure Ralf.

I will do that thanks!