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

[Anonymous]

Hai experts.

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

Thanks in advacnce

[adityaakshaya]

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.

[jyothish8807 · Accepted solution]

Hi Suman,

You can also refer the approach explained by jagan‌

Load Multiple excel sheets using For loop

Br,

KC

[Anonymous]

Thanks Akshaya

[Anonymous]

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.

[Anonymous]

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

values     

1,2,3

4

5

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

                   5

Thanks in advance

[jyothish8807]

Try like this:

A:

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;

[Anonymous]

Its working.

Thanks for giving suggistion.

[jyothish8807]

You are welcome