Importing Data from excel

[onetwothree123]

I have a excel sheet in following format :

Field Name 1

North

East

Line of business 1

West

Line of business 2

South

Line of business 3

While loading it in Qlikview can I differentiate and create two fields  as

Field Name 1 (Regions)         Field Name 2 (Line of Business)

North                                       Line of Business 1

East                                         Line of Business 2
South                                       Line of Business 3
West

If yes, How can I do it in qlikview.

If no, should I get it as two different fields in Excel and Import it in Qlikview

What should be the ideal scenario

[swuehl]

Is there any realtionship between Region and Line of Business or are they unrelated?

I.e. is North related to only LoB1, East to only LoB2 etc.?

I think it would be best if you could create two fields in Excel and read those fields into QV.

[onetwothree123]

Swuehl,

Each LoB 's are related to regions

[onetwothree123]

Swuehl,

I am getting this Excel from essbase cube.

and point of contact for it is saying that the way we have it in cubes, you will get it in one field.

So now I am puzzled how to seperate it in QV

[swuehl]

You'll first need to specify the logic, how the LoB are related to Regions when looking at the one field.

I first assumed that subsequent lines after a region define your related Lob, but that's not reproducing your second table (for example North --> LoB1)

[onetwothree123]

Each LoB has Six different Regions associated to them

LoB1 ----  North,East,West,South and two other

LoB2 ----  North,East,West,South and two other

and in Excel its just one field containing both regions and LoB's

[swuehl]

Again, I don't see a logic here, how are 'two others' related to LoBs (I assume it can be distinct 'two others' from LoB to LoB)?

If you read in the records of field name 1.

North

East

Line of business 1

West

Line of business 2

South

Line of business 3

How do you know which region is related to which LoB? If you can't tell a logic, you won't be able to program it in a QV code.

If it's all about a fixed number of Regions and LoB and they are not supposed to change frequently, you can hardcode the relations in an inline table:

LOAD * INLINE [

Region, Line of Business

West, wholesale

East, wholesale

North, wholesale

South, wholesale

OtherRegion1, wholesale

OtherRegion2, wholesale

West, retail

East, retail

North, retail

South, retail

OtherRegion3, retail

OtherRegion4, retail

];

[onetwothree123]

Okay I think I'm not being clear in my question.

There are three LoB's and each of them has six different regions.(same across all LoB's)

And the way I'm getting it in excel, both LoB's and six regions are sitting in one column

[swuehl]

OK, I think there are only few possible solutions.

a) define Regions and Line of Business in an INLINE table or excel file with separate columns. You'll need to control this information and you'll have to adapt any changes

b) If there is any kind of difference between Region and Line fo business values in that one field (like, Line of Businesses starts all with 'Li' and Regions never starts with 'Li', or Regions only one word, Line of Business always more than one word) you can separate the fields like this (and create a table with a combination of each Region and each LoB):

REGIONLOB:

LOAD [Field Name 1] as Region Resident EXCEL where not [Field Name 1] like 'Li*';

JOIN (REGIONLOB) LOAD [Field Name 1] as LoB Resident EXCEL where [Field Name 1] like 'Li*';

c) I assume that these Region / Line of Business table is not a data island, but linked to some fact table (maybe some Sales revenue per Line of Business). Then you can use this existing field to filter the values in Field Name 1:

FACT:

LOAD * INLINE [

Line of Business, Value

Line of business 1, 10

Line of business 2, 20

Line of business 3, 30

Line of business 1, 40

Line of business 2, 50

Line of business 3, 60

];

REGIONLOB:

LOAD [Field Name 1] as Region Resident EXCEL where not exists([Line of Business],[Field Name 1]);

JOIN (REGIONLOB) LOAD [Field Name 1] as LoB Resident EXCEL where  exists([Line of Business],[Field Name 1]);