topic Re: How to load multiple tabs with one xls file in QlikView

How to load multiple tabs with one xls file

prma7799 — Wed, 06 Jan 2016 09:17:48 GMT

Hello All,

How to load multiple tabs with one xls file.

PFA...

Re: How to load multiple tabs with one xls file

arulsettu — Wed, 06 Jan 2016 09:22:02 GMT

check this

Load Multiple excel sheets using For loop

Re: How to load multiple tabs with one xls file

prma7799 — Wed, 06 Jan 2016 09:26:35 GMT

Hi Arul,

I already tried this but i am getting error.

Thanks

Re: How to load multiple tabs with one xls file

arulsettu — Wed, 06 Jan 2016 09:31:43 GMT

what error

Re: How to load multiple tabs with one xls file

prma7799 — Wed, 06 Jan 2016 09:36:28 GMT

Cannot locate table in BIFF file

Concatenate(NPL)

LOAD * FROM

(biff, embedded labels, table is [V 1] )

Re: How to load multiple tabs with one xls file

jagan — Thu, 07 Jan 2016 05:25:11 GMT

Hi,

Try like this

Data:

LOAD RecNo() AS ID,

[Inv Date],

Month

FROM

[NPL.xls]

(biff, embedded labels, table is V1$);

LEFT JOIN(Data)

LOAD RecNo() AS ID,

Total,

Apr,

May

FROM

[NPL.xls]

(biff, embedded labels, table is V2$);

LEFT JOIN(Data)

LOAD RecNo() AS ID,

Jun,

Jul,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

[NPL.xls]

(biff, embedded labels, table is V3$);

Hope this helps you.

Regards,

jagan.

Re: How to load multiple tabs with one xls file

prma7799 — Thu, 07 Jan 2016 05:35:17 GMT

Hello Jagan,

This is again we are fetching data manually for all tabs.

We can't achieve this in one load statements as you mention here Load Multiple excel sheets using For loop ?

Re: How to load multiple tabs with one xls file

jagan — Thu, 07 Jan 2016 05:45:51 GMT

HI,

Try this script

LET vStartSheetNumber = 2;

LET vEndSheetNumber = 3;

Data:

LOAD RecNo() AS ID,

[Inv Date],

Month

FROM

[NPL.xls]

(biff, embedded labels, table is V1$);

FOR index = vStartSheetNumber TO vEndSheetNumber

LEFT JOIN(Data)

LOAD RecNo() AS ID,

FROM

[NPL.xls]

(biff, embedded labels, table is [V$(index)$]);

Re: How to load multiple tabs with one xls file

prma7799 — Thu, 07 Jan 2016 06:00:10 GMT

again same error come

annot locate table in BIFF file

NPL:

LOAD RecNo() AS ID,

[Inv Date],

Month

FROM

(biff, embedded labels, table is V1$)

Re: How to load multiple tabs with one xls file

settu_periasamy — Thu, 07 Jan 2016 06:05:12 GMT

Hi,

Another One..

DIRECTORY;
LET vStartSheetNumber = 1;
LET vEndSheetNumber = 3;
LET vExcelFileName = 'NPL';

// Generate Empty table
NPL:
LOAD RecNo() as Key,
          '' AS [Inv Date],
  '' AS Month
AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber
  if index=1 then
  JoinType='Concatenate'
  ELSE
     JoinType='Left Join'
    ENDIF
    $(JoinType)(NPL)
    LOAD RecNo() as Key,* FROM
    [$(vExcelFileName).xls](biff, embedded labels, table is V$(index)$);
NEXT

Re: How to load multiple tabs with one xls file

jagan — Thu, 07 Jan 2016 06:13:32 GMT

Hi,

Please find attached file it is working for me.

Load the excel file manually and replace this portion of script, with your excel file it is working for me. Are you using the same excel file xls or XLSX?

(biff, embedded labels,

Regards,

Jagan.