Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anitamelbye
Creator
Creator

Two columns, split it in to some kind of hierarky? A small puzzle, please help? :-)

Hi

I have been trying for a while to make something cool, and I am about to loose mye mind 😉

This is my simplified dataload, my acctual data contains MUCH more data
This is my dataload.jpg

How can I make my data in my load to connect and like this:

Want my dataload to look like this.jpg

When I press on Drammen, my two only valid options will be Konnerud & Åssiden                                

I can make connections/mapping/renaming in my load, but I dont know how to, can anyone help me?                                                         

So to sum it up:

1 digit is always Country

2 digits is always Community, shall be connectet to all Countries that starts with same first number

4 digits is always City, shall be connectet to all Communities starts with the same two number   
5 digits is always Places, shall be connectet to all Cities that starts with the same four number 

Its hard to explain, but please ask if you need a better explanation 🙂    

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

I have also attaced the example QlikView App as a QVW:

2015-01-08 Hierarchy.PNG

View solution in original post

12 Replies
Anonymous
Not applicable

Hi Anitha ,

Can you be more Specific so that we might play

ecolomer
Master II
Master II

See thjis examples:

p01.png

jyothish8807
Master II
Master II

Hi Anita,

You can create a custom fields like this:

LOAD * INLINE [

    Number, Name

    3, Norge

    30, Buskerud

    3000, Drammen

    30001, Konnerud

    30002, Assiden

];

Tab2:

load

if(Len(Number)=1,Number&'-'&Name) as Country,

if(Len(Number)=2,Number&'-'&Name) as Community,

if(Len(Number)=4,Number&'-'&Name) as City,

if(Len(Number)=5,Number&'-'&Name) as Place

resident Tab1;

still working on connectivity part.

Regards

KC

Best Regards,
KC
ecolomer
Master II
Master II

One possible solution to what you want to incorporate into the load level (= number of digits) and then use it as a discriminator column in which you must put each data.

In addition, you must create the "father" of whom depend each record. So 3000 - Drammen, his "father" is 30

trdandamudi
Master II
Master II

Anita,

Hope I understood your requirement the way you want and hope the below code will work for you..

Source_Load:
LOAD * INLINE [
Number, Name
3, Norge
30, Buskerud
3000,Drammen
30001, Konnerud
30002, Assiden
4, Norge1
40, Buskerud1
4000,Drammen1
40001, Konnerud1
40002, Assiden1
]
;

Tbl_Country:
LOAD
Number as Country_Key,
Number&'-'&Name as Country
Resident Source_Load
where Len(Number)=1;


Tbl_Community:
NoConcatenate
LOAD
Left(Number,1) as Country_Key,
Number as Community_Key,
Number&'-'&Name as Community
Resident Source_Load
where Len(Number)=2;


Tbl_City:
NoConcatenate
LOAD
Left(Number,2) as Community_Key,
Number as City_Key,
Number&'-'&Name as City
Resident Source_Load
where Len(Number)=4;

Tbl_Places:
NoConcatenate
LOAD
Left(Number,4) as City_Key,
Number as Places_Key,
Number&'-'&Name as Places
Resident Source_Load
where Len(Number)=5;


Drop Table Source_Load;

petter
Partner - Champion III
Partner - Champion III

I have also attaced the example QlikView App as a QVW:

2015-01-08 Hierarchy.PNG

petter
Partner - Champion III
Partner - Champion III

Futhermore this document is very helpful:

Hierarchies

and perhaps even this blog-post:

Unbalanced, n-level hierarchies

Good luck

MarcoWedel

very nice

regards

Marco

ecolomer
Master II
Master II

your welcome