Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Swuehl,
Each LoB 's are related to regions
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
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)
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
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
];
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
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]);