topic Re: How to extract data into qlikview with multiple sheets in one excel in QlikView

How to extract data into qlikview with multiple sheets in one excel

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hai experts.

I want load data into qlikview with multiple sheets in one excel.

Thanks in advacnce

Re: How to extract data into qlikview with multiple sheets in one excel

adityaakshaya — Wed, 20 Jun 2018 04:52:10 GMT

Hi Suman,

Please follow the below steps to pick data from all sheets at one go.

Step 1: Create a Connection and select the folder where your file reside and name it as "Data"

Step 1: Create a ODBC connection with same Excel file.

Step 2 : Count the number of Tables(Sheets) by use below Script

Tables:

SQLTABLES;

let vNoOfTables=NoOfRows('Tables');

Step 3: Run the Loop to pull data from all the sheets by using code below

For i = 0 to $(vNoOfTables)-1

Let vSheetName=subfield(peek('TABLE_NAME', i,'Tables'),'$',1);

$(vSheetName):

LOAD *
FROM
[Lib://Data/Sample Data File.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)')

;
NEXT i

Regards,

Akshaya

PS - if you find this response as Correct and helpful, Please mark it as Correct and Helpful.

Re: How to extract data into qlikview with multiple sheets in one excel

jyothish8807 — Wed, 20 Jun 2018 05:31:47 GMT

Hi Suman,

You can also refer the approach explained by jagan‌

Load Multiple excel sheets using For loop

Br,

Re: How to extract data into qlikview with multiple sheets in one excel

Anonymous — Wed, 20 Jun 2018 05:34:04 GMT

Thanks Akshaya

Re: How to extract data into qlikview with multiple sheets in one excel

Anonymous — Wed, 20 Jun 2018 05:56:52 GMT

Hi Jyothish

Thanks and give me a solution of given example

my table is like this

unique,doc

123,(343,241)

122,(243,546)

Now I want output like this(i,e.In first output is 1st value doc & in second output is maximum value of doc).

first value maximum value

343 343

243 546

In different outputs.

thanks in advance.

Re: How to extract data into qlikview with multiple sheets in one excel

Anonymous — Wed, 20 Jun 2018 06:02:55 GMT

I want split values in excel sheet cell & my record is like below type

values

1,2,3

6,7

now I want the output is in the type of split values & count of values

values split values count

1,2,3 1 3

4 2 1

5 3 1

6,7 4 2

Thanks in advance

Re: How to extract data into qlikview with multiple sheets in one excel

jyothish8807 — Wed, 20 Jun 2018 15:29:28 GMT

Try like this:

Load

Unique,

Subfield(doc) as newdoc,

Subfield(doc,',',1) as firstvalue

Resident table;

Noconcatenate

Load

Unique,

Firstvalue,

Max(newdoc) as maximumvalue

Resident A

Group by Unique,

Firstvalue;

Order by unique;

Drop table A;

Re: How to extract data into qlikview with multiple sheets in one excel

Anonymous — Wed, 20 Jun 2018 15:56:52 GMT

Its working.

Thanks for giving suggistion.

Re: How to extract data into qlikview with multiple sheets in one excel

jyothish8807 — Wed, 20 Jun 2018 15:59:55 GMT

You are welcome