Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Any Help

Hi,

I have two table,

first table is :

LOAD ID,

    Type,

    Code,

    Description

in description i have country names,zone names,region names,state names,district names,territory names,town names based on ID.

second table is :

LOAD GeoID,

    CountryID,

    ZoneID,

    RegionID,

    StateID,

    DistrictID,

    TerritoryID,

    TownID

In this table i am having only the IDs for related to first table Description.

Now i want to create the hierarchy from country name to town name.How to overcome from this problem.How to create a hierarchy

any help

Hello Kaushik,

Thanks for helping,

PFA is my first table and second table:

I want Hierarchy from Country to Town.

regards,

baru

regards,

baru

1 Solution

Accepted Solutions

Re: Any Help

I see you want the data in a hierarchy. I've done that in the attached qvw. Can you see if that's what you need?


talk is cheap, supply exceeds demand
12 Replies

Re: Any Help

Hi,

     You can try this way.

    LOAD GeoID,

         CountryID,

         ZoneID,

         RegionID,

         StateID,

         DistrictID,

         TerritoryID,

         TownID

     From XYZ;

     Left join

   

     LOAD ID as GeoID,

          Description as GeoName

     From PQR;

     Left join

   

     LOAD ID as CountryID,

          Description as CountryName

     From PQR;

     Like wise you can try for all the fields and at the end you will have table where you will get ID's and Name.

Regards,

Kaushik Solanki

Not applicable

Re: Any Help

Hi Kaushik,

thanks for reply,

In my second table GeoId and first table ID is different.

First table ID's are in second table CountryID,ZoneID,RegionID, StateID,DistrictID,TerritoryID,TownID

how can i get this in hierarchy based

regards,

baru

Re: Any Help

Do you mean that the ID's of First table and Second Table is different?

Regards,

Kaushik Solanki

Not applicable

Re: Any Help

Hello Kaushik,

thanks for help,

i attached two excels please find.,

regards,

baru

Not applicable

Re: Any Help

Sorry kaushik,

this is my second table

GeoIDCountryIDZoneIDRegionIDStateIDDistrictIDTerritoryIDTownIDClassificationID
14647557911311511681150
24647547310711611681150
3464749649011711681150
4464757116710211811681150
5464757116710411911681150
64647557911412011681150
7464853689712110671150
8464757116710412211681150
9464751659212311681150
104647517510912411681150
11464749649012511681150
1246485059801268781150
134647546910012711681150
14464757116710212811681150
154647546910012911681150
1646485260861308321150
17464755798113111681150
184648507410813210781150
19464852679813310331150
20464852679813410341150
21464749649113511681150
22464850598313611251150
2346475770116513711681150
24464852679813810391150
2546474976116413911681150
264647577010614011681150
274648507410814110741150
28464749649114211681150
29464751659214311681150
304648507410814410861150
314647546910014511681150
3246485368941469131150
334647546910014711681150
3446485368941489181150
354647546910014911681150
3646485059801508841150
374647517510915111681150
3846485862881528421150
39464858781121539421150
404648507410815410921150
414648587711115510011150
4246474976116415611681150
43464755798115711681150
44464751638915811681150
45464755618515911681150
46464757116710416011681150
474647547310716111681150
48464852679916210511150
49464850598216311281150
50464858781121649511150
51464751659216511681150
53464852679816710421150
54464757116710316811681150
554647557911416911681150
56464850598217011101150
57464751659217111681150
5846475770116517211681150
594647547310717311681150
6046485260871748371150
61464751659217511681150
624647546910017611681150
63464852679817710441150
644648506611661788711150
654647547310717911681150
6646475770116518011681150
6746475770116518111681150
68464755618518211681150
69464757116710418311681150
704647517510918411681150
71464749649018511681150
7246474976116418611681150
73464751638918711681150
7446474976116418811681150
754647547310718911681150
76464749649019011681150
77464850598319111311150
7846485066931928931150
7946485862881938461150
804647557911419411681150
81464751659219511681150
8246485066931969031150
8346485260861978331150
844648587711119810111150
8546485368951999231150
8746474976116420111681150
8946485059802038861150
90464757116710220411681150
91464755798120511681150
924647517510920611681150
93464858771102079711150
94464852679920810531150
954647546910020911681150
9646485368962109301150
974647567210121111681150
98464749649021211681150
994647557911321311681150
1004647546910021411681150
101464755618521511681150
10246485260872168391150
103464757116710421711681150
104464757116710421811681150
105464749649121911681150
106464858771102209901150
107464850598222111141150
1084647567210122211681150
109464754618422311681150
11046485066932249091150
1114647547310722511681150
112464757116710222611681150
113464850598222711171150
114464755618522811681150
11546485368962299371150
1164647517510923011681150
1174648587711123110191150
1184647577110523211681150
119464755618523311681150
120464853689723410701150
1214647577010623511681150
122464749649023611681150
123464755618523711681150
124464850598323811381150
1254648507410823911001150
126464751659224011681150
127464858781122419591150
1284648507410824211031150
129464757116710324311681150
1304647517510924411681150
1314647577110524511681150
1324647547310724611681150
1334647546910024711681150
134464852679924810561150
135464850598324911411150
136464751638925011681150
13746475770116525111681150
138464749649125211681150
1394648587711125310211150
1404648587711125410271150
1414647567210125511681150
14346485059802578901150
14446485368962589381150
145464858771102599931150
146464853689726010721150
14746485260862618351150
14846475770116526211681150
1494647557911426311681150
15046485862882648441150
151464850598226511211150
152464850598326611401150
15346475473107116116843
1554647496490117116843
15646475671105226116843
1574647561167104122116843
1594648536895121106743
1604647516592123116843
16146475175109124116843
1624647496491142116843
16446475469100127116843
16546475671105128116843
16646475469100209116843
16746475474108154109243
17046475474108132107843
1734647516592143116843
1744647496490236116843
176464749701165137116843
17746475474108144108643
179464749761164139116843
1804647561167104140116843
18146475469100145116843
18446475175109151116843
185464853628815284243
1864648537811215394243
18746475469100149116843
18846485377111155100143
1924647516389158116843
1934647546185159116843
1944647561167104160116843
19546475473107161116843
1974648537811216495143
1984647516592165116843
2004647561167103168116843
2034647516592171116843
20446475473107173116843
205464749701165172116843
2074647516592175116843
21046475473107179116843
211464749701165180116843
212464749701165181116843
213464749761164186116843
21446475175109184116843
2154647516389187116843
2164647496490185116843
2174647546185182116843
218464749761164188116843
21946475473107189116843
2234647516592195116843
225464853689519992343
22746475469100147116843
228464749761164201116843
2294648536895202105943
23046485377111198101143
2324647561167103245116843
23446475175109206116843
2354648537711020797143
2384647496490212116843
23946475469100176116843
24046475672101211116843
2414647546185228116843
24246475473107225116843
2434648537711022099043
2444647561167104218116843
2454647496491219116843
24846475672101222116843
25046475175109230116843
25146485377111231101943
25246475671105232116843
2534647546185233116843
2544648536897234107043
2554647561167104235116843
2564647546185237116843
2574647516592240116843
25846475474108239110043
2594648537811224195943
26046475474108242110343
26146475175109244116843
26346475473107246116843
26446475469100247116843
2654647516389250116843
2664647496491252116843
26746485377111253102143
26846475672101255116843
2694648537711025999343
2704648536895256106343
27246485377111254102743
2734648536897260107243
274464749761164156116843
275464749701165262116843
27846475473107173116844
2794647517981115116844
2804647566490117116844
28146475671105226116844
2824647561167104119116844
2834647517981120116844
2844648536897121106744
28546475175109123116844
28646475175109184116844
2874647566490125116844
28946475469100145116844
2904647561167104128116844
29146475469100129116844
29246475474108154109244
2944647517981205116844
2954647566490142116844
29646475474108132107844
29946475175109143116844
3004647566490135116844
302464756701165137116844
30346475474108144108644
305464756701165139116844
3064647561167104140116844
307464853689614691344
308464853689614891844
30946475175109151116844
310464853628815284244
31246475469100247116844
3164647517981157116844
3174647516389158116844
3184647546185182116844
3194647561167104183116844
32046475473107161116844
32346475175109171116844
32546475671105168116844
33146475473107246116844
332464756701165180116844
333464756701165181116844
334464756701165186116844
3354647516389187116844
3364647566490190116844
337464756701165188116844
3394647517981194116844
34146475175109195116844
343464853689519992344
34646475469100147116844
347464756701165156116844
3484648536895202105944
35146475671105204116844
35246475175109206116844
354464853689621093044
3564647566490212116844
35746475469100214116844
35846475672101211116844
3594647546185228116844
3624647561167104218116844
3634647561167104217116844
3644647566490252116844
36646475469100223116844
36746475672101222116844
369464853689622993744
3704648536288231101944
37146475671105232116844
3724647546185233116844
3734648536897234107044
3744647561167104235116844
3764647546185237116844
37746475474108239110044
37946475671105245116844
38146475672101255116844
3844648536895256106344
385464853689625893844
3874648536897260107244
3884647517981263116844
3894647566490117116845
390464850608613083245
3914647566490142116845
3924647566490137116845
3934647516389151116845
39646475169100154109245
3974647516389158116845
39846475672101168116845
39946475169100173116845
40046485066116617887145
4014647566490180116845
4024647566490186116845
40446475169100147116845
4054647516389205116845
40746475672101211116845
40846475672101218116845
40946475169100223116845
41046475169100228116845
41146475672101232116845
Not applicable

Re: Any Help

Locations:

LOAD GeoID,

     CountryID,

     ZoneID,

     RegionID,

     StateID,

     DistrictID,

     TerritoryID,

     TownID,

     ClassificationID

FROM

[second table.xls]

(biff, embedded labels, table is Sheet1$);

left Join

LOAD ID as CountryID,

     Description as Country

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

left Join

LOAD ID as ZoneID,

     Description as Zone

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

left Join

LOAD ID as RegionID,

     Description as Region

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

left Join

LOAD ID as StateID,

     Description as State

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

Left Join

left Join

LOAD ID as DistrictID,

     Description as District

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

left Join

LOAD ID as TerritoryID,

     Description as Territory

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

Left Join

LOAD ID as TownID,

     Description as Town

FROM

[first table.xls]

(biff, embedded labels, table is Sheet1$);

Regards

Re: Any Help

Try this:

Table2:

mapping LOAD ID , Description as GeoName

From .... ;

Table1:

LOAD GeoID,

         CountryID,

         applymap('Table2',CountryID) as CountryName,

         ZoneID,

         applymap('Table2',ZoneID) as ZoneName,

         RegionID,

         applymap('Table2',RegionID) as RegionName,

         StateID,

         applymap('Table2',StateID) as StateName,

         DistrictID,

         applymap('Table2',DistrictID) as DistrictName,

         TerritoryID,

         applymap('Table2',TerritoryID) as TerritoryName,

         TownID,

         applymap('Table2',TownID) as TownName

From .....;

edit: fixed applymap references to Table2


talk is cheap, supply exceeds demand
Not applicable

Re: Any Help

one correction to the above: in applymap('Table2') has to be used

Re: Any Help

I see you want the data in a hierarchy. I've done that in the attached qvw. Can you see if that's what you need?


talk is cheap, supply exceeds demand
Community Browser