topic Re: Uploading data in different sheets in QlikView

Uploading data in different sheets

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

I have same data model which are in different sheets but relation to different centers such as centre A, B ,C etc. but same dimensions & expressions. Can I load the data in different sheets at once if so what steps to be followed. Appreciate if explained steps by steps!

Regards

Neville

Re: Uploading data in different sheets

nevilledhamsiri — Tue, 19 Sep 2017 14:34:15 GMT

Please, Help me on this

Regards

Neville

Re: Uploading data in different sheets

Gysbert_Wassenaar — Tue, 19 Sep 2017 15:01:11 GMT

Data is not uploaded in sheets. Data is loaded into the data model. There can only be one data model in a Qlik Sense app or Qlikview document. After loading the data it can be shown in chart and table objects (and other objects) on sheets.

The obvious way to deal with different centers A, B and C is to select the center you wish to see. You can use a filter pane (Qlik Sense) or a List box (Qlikview) with the field that contains the center values as dimension.

Re: Uploading data in different sheets

nevilledhamsiri — Tue, 19 Sep 2017 16:19:17 GMT

Dear Gisbert,

Please see my small data array. 1st one (EM,EM1,EM2) in one sheet in excel & the other two RA, & KW goes in other two sheets. If I load this file, will the data in all three sheets be loaded?. I loaded but always the selected sheet data only loaded. I need data from all three sheets to be loaded once.

Please see whether this is possible.

Regards

Neville

BRANCH PROFITS

EM 500

EM1 250

EM2 350

BRANCH PROFITS

RA 500

RA1 250

RA2 350

BRANCH PROFITS

KW 500

KW1 250

KW2 350

Re: Uploading data in different sheets

prieper — Wed, 20 Sep 2017 10:05:05 GMT

Are you talking on Excel-Sheets?

You may only have to concatenate the data...

Re: Uploading data in different sheets

Gysbert_Wassenaar — Wed, 20 Sep 2017 10:38:34 GMT

You need to create a load statement for each sheet in your excel file(s).

In the File Wizard of the load editor all the sheets of an excel file will be shown in a list of tables. You select which table from that list you want to load. There is no option to automatically load all the sheets from on or more excel files. If you need to do that you'll have to use a custom script like in the example here: Re: Loading Multiple Excel Files and Multiple Excel sheets

Re: Uploading data in different sheets

Anonymous — Wed, 20 Sep 2017 10:44:32 GMT

Depends on how you want your data.

From your data model below:

BRANCH PROFITS

EM 500

EM1 250

EM2 350

BRANCH PROFITS

RA 500

RA1 250

RA2 350

BRANCH PROFITS

KW 500

KW1 250

KW2 350

I will simply load in this format:

Sheet 1: Branch A

LOAD

Branch as Branch

Branch as BranchA

FROM ...

Sheet 2: BranchB

LOAD

Branch as Branch

Branch as BranchB

FROM ...

Sheet 3: BranchC

LOAD

Branch as Branch

Branch as BranchC

FROM ...

Why I loaded in duplicates is so that you have a key that connects all in your data model. From this you can do calculations on all segments at once or if you choose do individual data exploration with the duplicates

Regards

Peter

Re: Uploading data in different sheets

ramasaisaksoft — Wed, 20 Sep 2017 12:19:50 GMT

I think Section Access concept will help to you.

Means

if A is Open the file it will load data for A person data

Ex :-

LOAD * INLINE [

ACCESS, USERID, YEAR

ADMIN, DOMAIN/ADMIN, *

USER, DOMAIN\A, 2011

USER, DOMAIN\B, 2015

USER, DUMMY, 2012

USER, DUMMY, 2013

USER, DUMMY, 2014

USER, DUMMY, 2016

];

Go to Settings -- Document Properties -- Opening

Tick All Three

Initial Data Reduction Based on Section Access

Strict Exclusion

Prohibit Binary Load

Section Access REDUCTION example

Re: Uploading data in different sheets

pradosh_thakur — Wed, 20 Sep 2017 12:40:07 GMT

here my excel sheet has 3 sheet name is 0,1,2 for simplification . i have used for loop to load them at once.let me know if it is helpful or you need any help.

FOR i = 0 to 3

LOAD [BRANCH]

[ PROFITS ]

FROM

[excel files\dummy_excel.xlsx]

(ooxml, embedded labels, table is $(i));

NEXT i

regards

Pradosh