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

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

anitamelbye — Thu, 08 Jan 2015 21:16:23 GMT

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 🙂

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

Anonymous — Sun, 11 Jan 2015 07:37:55 GMT

Hi Anitha ,

Can you be more Specific so that we might play

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

ecolomer — Sun, 11 Jan 2015 08:04:31 GMT

See thjis examples:

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

jyothish8807 — Sun, 11 Jan 2015 08:10:33 GMT

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

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

ecolomer — Sun, 11 Jan 2015 08:20:28 GMT

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

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

trdandamudi — Sun, 11 Jan 2015 09:45:42 GMT

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;

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

petter — Sun, 11 Jan 2015 10:44:26 GMT

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

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

petter — Sun, 11 Jan 2015 10:49:46 GMT

Futhermore this document is very helpful:

Hierarchies

and perhaps even this blog-post:

Unbalanced, n-level hierarchies

Good luck

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

MarcoWedel — Sun, 11 Jan 2015 18:10:57 GMT

very nice

regards

Marco

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

ecolomer — Sun, 11 Jan 2015 19:40:06 GMT

your welcome

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

Sun, 11 Jan 2015 23:28:35 GMT

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;

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

Mon, 12 Jan 2015 03:58:48 GMT

Super Buenisimo!,

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

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

MarcoWedel — Mon, 12 Jan 2015 23:59:57 GMT

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;

hope this helps

regards

Marco