Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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;
Super Buenisimo!,
gave me some ideas on how to present some bank accounts 🙂
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