topic Re: Two tables in One excel sheet in QlikView

Two tables in One excel sheet

Mon, 10 Nov 2014 05:08:11 GMT

In single Excel sheet i have two tables how to load that data into Qlikview

Regards,

Singam.

Re: Two tables in One excel sheet

vardhancse — Mon, 10 Nov 2014 05:09:44 GMT

If it was in single excel sheet and multiple workbooks, after selecting the excel sheet we can select the workbook.

In that way if load multiple tables from single excel sheet and multiple work books

Re: Two tables in One excel sheet

amit_saini — Mon, 10 Nov 2014 05:10:41 GMT

Amar,

Please share you excel sheet.

Thanks,

Re: Two tables in One excel sheet

Mon, 10 Nov 2014 05:20:25 GMT

Hi Sasi

I have data in one workbook multiple excel sheet but in one excel sheet i have two tables .I loaded that data the data is showing only in table viewer when we click on preview. But the field names are not showing in script level.

Regards,

Singam.

Re: Two tables in One excel sheet

danieloberbilli — Mon, 10 Nov 2014 05:23:46 GMT

If you use the File Wizard, you just click on the X-icons of the columns that you don't want to load:

So 1st: you only load the columns of table 1 and then afterwards you open the wizard again and load the columns of table 2

Re: Two tables in One excel sheet

its_anandrjs — Mon, 10 Nov 2014 05:25:58 GMT

Hi,

You can get the help of the transformation steps option available in the file wizard when you load the excel sheet

and then click on the button Enable transformation step and then you can find many options.

Regards

Anand

Re: Two tables in One excel sheet

Mon, 10 Nov 2014 05:27:19 GMT

Hi Amit

Please find the attachment

regards,

Singam.

Re: Two tables in One excel sheet

SergeyMak — Mon, 10 Nov 2014 05:37:30 GMT

Hi,

When you load data from the sheet with two tables press next and Enable Transformation step.

Then delete what you don't need.

Next time do otherwise..

Re: Two tables in One excel sheet

amit_saini — Mon, 10 Nov 2014 05:48:59 GMT

Try this:

Step1:

Then press Alt F11, double click on any sheets in the left of the opened window and copy the below code :

Sub renameSheets()
Dim iSheetCount
For iSheetCount = 1 To Sheets.Count
Sheets(iSheetCount).Name = iSheetCount
Next iSheetCount
End Sub

Step 2: Then run by clicking the run button like below

Step3:

Save as the file in xlsx fomat.

Step4 :

Then Open the QVW, Press Cntrl E, Assuming the file contain 4 sheets.
Eg. for loop=1 to 4

Step5:

Reload QVW

Thanks,

Re: Two tables in One excel sheet

Mon, 10 Nov 2014 06:15:48 GMT

Hi Amit

Could you please brief about it to which excel sheet i have to do

what i you said i din't understand

Re: Two tables in One excel sheet

amit_saini — Mon, 10 Nov 2014 06:28:13 GMT

Amar,

Step1:

First open the file “Daily Operational Report_28 02 2011 ” delete sheets where no data required for reports.

Step2:

Then press Alt F11, double click on any sheets in the left of the opened window and copy the below code as shown in the below picture.

Sub renameSheets()
Dim iSheetCount
For iSheetCount = 1 To Sheets.Count
Sheets(iSheetCount).Name = iSheetCount
Next iSheetCount
End Sub

Step3:

Then run by clicking the run button like below

Step4:

Save as the file in xlsx fomat.

Step5:

Then Open the QVW, Press Cntrl E, go. Assuming the file contain 290 sheets.
Eg. for loop=1 to 290

Step6:

Reload QVW

Hope this will help you.

Thanks,

Re: Two tables in One excel sheet

Mon, 10 Nov 2014 15:26:38 GMT

Hi Amit

I need all sheets data for reporting

Regards,

Singam,

Re: Two tables in One excel sheet

Mon, 10 Nov 2014 15:40:22 GMT

Hi,

If you have two separate tables like sales and budget then load separatly like this

Sales:

Load *

from (excel path,table name is Sales);

Budget:

Load *

from (excel path,table name is Budget);

if you have sheet1,sheet2 like this you have two table with same field names using for loop load the data at a time like

for i=1 to 2;

Load * from (excelpath, Sheet($i));

Next;

In single sheet you have two tables then using the Enable Transformation Step you can load the data into qlikview.

Just play with that property you will get the required output

Re: Two tables in One excel sheet

Tue, 11 Nov 2014 09:09:13 GMT

Hi Rakesh

The two tables in one sheet only it is not in different sheets and it is not loading properly please find the attachment in the above conversion