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 🙂    

12 Replies
Not applicable

Anita,

           First of all the hierarchy presents a little of complexity since from 30 you jump to 3000 in the hierarchy, and not 300 , so here is a simple tricky script of loads and drops that finally make use of the hierarchy function to present the data in the way you want using 3 different objects, table box, list box with tree view option and a pivot table.

Hope this helps, attached is the qvw file. and here is the final script:

stage1:

LOAD * INLINE [

Number, Name

3, Norge

30, Buskerud

3000,Drammen

30001, Konnerud

30002, Assiden

4, Norge1

40, Buskerud1

4000,Drammen1

40001, Konnerud1

40002, Assiden1

];

NoConcatenate

stage2:

load *,

     len(Number) as PosNumber,

     if (len(Number)=1 , 0,

           if (len(Number)=2, 1,

                   len(Previous(Number))

         )) as PreviousPosNumber Resident stage1;

drop table stage1;

NoConcatenate

stage3:

LOAD

      *,

         if (PosNumber=PreviousPosNumber, left(Number,PreviousPosNumber-1),left(Number,PreviousPosNumber))

       as ParentID

Resident stage2 order by  PosNumber  ;

drop table stage2;

NoConcatenate

finalStage:    

   Hierarchy(Number,ParentID,Name,ParentsOnly,Name)

   LOAD      Number,ParentID,Name

   Resident stage3;

RENAME Fields Name1 to Country, Name2 to Community, Name3 to City, Name4 to Places;

drop table stage3;

sc_147803_2.png

Not applicable

Super Buenisimo!,

gave me some ideas on how to present some bank accounts 🙂

MarcoWedel

Hi,

another more generic approach could be:

tabLocLevel:

LOAD *, Previous(digits) as prevDig

Inline [

digits, LocLevel

1, Country

2, Community

4, City

5, Place

];

mapLocLevel:

Mapping LOAD 'Name'&RecNo(), LocLevel Resident tabLocLevel;

tabLocation:

Hierarchy(Number, parentNum, Name,,Name,'LocPath','/','LocLvlDepth')

LOAD *,

    Left(Number, Lookup('prevDig', 'digits', Len(Number), 'tabLocLevel')) as parentNum

Inline [

    Number, Name

    3, Norge

    30, Buskerud

    3000, Drammen

    30001, Konnerud

    30002, Åssiden

    30003, Austad-Fjell

    30004, Bragernes

    30005, Gulskogen

    30006, Skoger

    30007, Strømsø-Danvik

    30008, Tangen-Åskollen

    31, Oppland

    3100, Lillehammer

    32, Hordaland

    3200, Bergen

    32001, Arna

    32002, Bergenhus

    32003, Fana

    32004, Fyllingsdalen

    32005, Laksevåg

    32006, Ytrebygda

    32007, Årstad

    32008, Åsane

];

RENAME Fields using mapLocLevel;

DROP Table tabLocLevel;

QlikCommunity_Thread_147803_Pic1.JPG

QlikCommunity_Thread_147803_Pic2.JPG

hope this helps

regards

Marco