topic Re: Loading in an additional sheet from excel in QlikView

Loading in an additional sheet from excel

Wed, 25 Nov 2020 16:16:04 GMT

Hi There,

I have an excel file with 3 sheets which have already been loaded into QlikView and would like to load in a 4th sheet. I have 5 of such files which all have the same characteristics!

Below is the breakdown of the script to load-in 3 sheets. Please note that:-

Sheet1 and Sheet3 have 9 rows whereas Sheet2 has 10 rows. This is the same for all the other files

========================================================

FOR j=0 to 2

LET vDataType=if($(j)=0,'Sheet1',

if($(j)=1,'Sheet3','Sheet2' ));

LET vDataTypeQV=if($(j)=0,'SheetNo1',

if($(j)=1,'SheetNo3','SheetNo2'));

LET vHeaderRecordCount=if($(j)=0,'9',

if($(j)=1,'10','9'));

========================================================

Please note the following for further details:

*vNoSites*	10
*vDataType*	Sheet2
*vDataTypeQV*	SheetNo2
*vHeaderRecordCount*	9
*vSite*	AREA

What is the correct syntax to add sheet4 please?

Thanks,

Re: Loading in an additional sheet from excel

boorgura — Wed, 13 Jul 2016 14:56:27 GMT

========================================================

FOR j=0 to 3

LET vDataType='Sheet' & $(j)+1 ;

LET vDataTypeQV='SheetNo' & $(j)+1 ,

LET vHeaderRecordCount=if($(j)= 2,'10', ,9,);

========================================================

Assuming only 3rd sheet has 10 rows and rest all have 9.

Re: Loading in an additional sheet from excel

Wed, 13 Jul 2016 16:09:47 GMT

Thanks but would I not need to add 'Sheet4' somewhere?

Re: Loading in an additional sheet from excel

boorgura — Wed, 13 Jul 2016 18:19:22 GMT

We are updating the loop counter.

And are we are deriving the sheet numbers dynamically. so even if you 10 sheets, you can just update the loop counter.

Of course this would work only if the format and everything is identical.

Or you will have to handle them using conditions like you did for sheet 3.

Re: Loading in an additional sheet from excel

Thu, 14 Jul 2016 09:39:02 GMT

Yes,

I would like to know what syntax to use considering the conditions applied for sheet 3 please.

Re: Loading in an additional sheet from excel

boorgura — Thu, 14 Jul 2016 12:10:49 GMT

The above mentioned is the syntax - assuming 10 columns for sheet 3 and 9 for the rest