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

[anitamelbye]

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

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

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 🙂    

[Anonymous]

Hi Anitha ,

Can you be more Specific so that we might play

[ecolomer]

See thjis examples:

[jyothish8807]

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

[ecolomer]

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]

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 · Accepted solution]

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

[petter]

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]

your welcome