topic Re: Any Help in QlikView

Any Help

Sat, 27 Oct 2012 10:16:15 GMT

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

Re: Any Help

kaushiknsolanki — Sat, 27 Oct 2012 10:32:14 GMT

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

Re: Any Help

Sat, 27 Oct 2012 11:04:11 GMT

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

kaushiknsolanki — Sat, 27 Oct 2012 11:11:21 GMT

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

Regards,

Kaushik Solanki

Re: Any Help

Sat, 27 Oct 2012 11:21:30 GMT

Hello Kaushik,

thanks for help,

i attached two excels please find.,

regards,

baru

Re: Any Help

Sat, 27 Oct 2012 11:26:23 GMT

Sorry kaushik,

this is my second table

GeoID	CountryID	ZoneID	RegionID	StateID	DistrictID	TerritoryID	TownID	ClassificationID
1	46	47	55	79	113	115	1168	1150
2	46	47	54	73	107	116	1168	1150
3	46	47	49	64	90	117	1168	1150
4	46	47	57	1167	102	118	1168	1150
5	46	47	57	1167	104	119	1168	1150
6	46	47	55	79	114	120	1168	1150
7	46	48	53	68	97	121	1067	1150
8	46	47	57	1167	104	122	1168	1150
9	46	47	51	65	92	123	1168	1150
10	46	47	51	75	109	124	1168	1150
11	46	47	49	64	90	125	1168	1150
12	46	48	50	59	80	126	878	1150
13	46	47	54	69	100	127	1168	1150
14	46	47	57	1167	102	128	1168	1150
15	46	47	54	69	100	129	1168	1150
16	46	48	52	60	86	130	832	1150
17	46	47	55	79	81	131	1168	1150
18	46	48	50	74	108	132	1078	1150
19	46	48	52	67	98	133	1033	1150
20	46	48	52	67	98	134	1034	1150
21	46	47	49	64	91	135	1168	1150
22	46	48	50	59	83	136	1125	1150
23	46	47	57	70	1165	137	1168	1150
24	46	48	52	67	98	138	1039	1150
25	46	47	49	76	1164	139	1168	1150
26	46	47	57	70	106	140	1168	1150
27	46	48	50	74	108	141	1074	1150
28	46	47	49	64	91	142	1168	1150
29	46	47	51	65	92	143	1168	1150
30	46	48	50	74	108	144	1086	1150
31	46	47	54	69	100	145	1168	1150
32	46	48	53	68	94	146	913	1150
33	46	47	54	69	100	147	1168	1150
34	46	48	53	68	94	148	918	1150
35	46	47	54	69	100	149	1168	1150
36	46	48	50	59	80	150	884	1150
37	46	47	51	75	109	151	1168	1150
38	46	48	58	62	88	152	842	1150
39	46	48	58	78	112	153	942	1150
40	46	48	50	74	108	154	1092	1150
41	46	48	58	77	111	155	1001	1150
42	46	47	49	76	1164	156	1168	1150
43	46	47	55	79	81	157	1168	1150
44	46	47	51	63	89	158	1168	1150
45	46	47	55	61	85	159	1168	1150
46	46	47	57	1167	104	160	1168	1150
47	46	47	54	73	107	161	1168	1150
48	46	48	52	67	99	162	1051	1150
49	46	48	50	59	82	163	1128	1150
50	46	48	58	78	112	164	951	1150
51	46	47	51	65	92	165	1168	1150
53	46	48	52	67	98	167	1042	1150
54	46	47	57	1167	103	168	1168	1150
55	46	47	55	79	114	169	1168	1150
56	46	48	50	59	82	170	1110	1150
57	46	47	51	65	92	171	1168	1150
58	46	47	57	70	1165	172	1168	1150
59	46	47	54	73	107	173	1168	1150
60	46	48	52	60	87	174	837	1150
61	46	47	51	65	92	175	1168	1150
62	46	47	54	69	100	176	1168	1150
63	46	48	52	67	98	177	1044	1150
64	46	48	50	66	1166	178	871	1150
65	46	47	54	73	107	179	1168	1150
66	46	47	57	70	1165	180	1168	1150
67	46	47	57	70	1165	181	1168	1150
68	46	47	55	61	85	182	1168	1150
69	46	47	57	1167	104	183	1168	1150
70	46	47	51	75	109	184	1168	1150
71	46	47	49	64	90	185	1168	1150
72	46	47	49	76	1164	186	1168	1150
73	46	47	51	63	89	187	1168	1150
74	46	47	49	76	1164	188	1168	1150
75	46	47	54	73	107	189	1168	1150
76	46	47	49	64	90	190	1168	1150
77	46	48	50	59	83	191	1131	1150
78	46	48	50	66	93	192	893	1150
79	46	48	58	62	88	193	846	1150
80	46	47	55	79	114	194	1168	1150
81	46	47	51	65	92	195	1168	1150
82	46	48	50	66	93	196	903	1150
83	46	48	52	60	86	197	833	1150
84	46	48	58	77	111	198	1011	1150
85	46	48	53	68	95	199	923	1150
87	46	47	49	76	1164	201	1168	1150
89	46	48	50	59	80	203	886	1150
90	46	47	57	1167	102	204	1168	1150
91	46	47	55	79	81	205	1168	1150
92	46	47	51	75	109	206	1168	1150
93	46	48	58	77	110	207	971	1150
94	46	48	52	67	99	208	1053	1150
95	46	47	54	69	100	209	1168	1150
96	46	48	53	68	96	210	930	1150
97	46	47	56	72	101	211	1168	1150
98	46	47	49	64	90	212	1168	1150
99	46	47	55	79	113	213	1168	1150
100	46	47	54	69	100	214	1168	1150
101	46	47	55	61	85	215	1168	1150
102	46	48	52	60	87	216	839	1150
103	46	47	57	1167	104	217	1168	1150
104	46	47	57	1167	104	218	1168	1150
105	46	47	49	64	91	219	1168	1150
106	46	48	58	77	110	220	990	1150
107	46	48	50	59	82	221	1114	1150
108	46	47	56	72	101	222	1168	1150
109	46	47	54	61	84	223	1168	1150
110	46	48	50	66	93	224	909	1150
111	46	47	54	73	107	225	1168	1150
112	46	47	57	1167	102	226	1168	1150
113	46	48	50	59	82	227	1117	1150
114	46	47	55	61	85	228	1168	1150
115	46	48	53	68	96	229	937	1150
116	46	47	51	75	109	230	1168	1150
117	46	48	58	77	111	231	1019	1150
118	46	47	57	71	105	232	1168	1150
119	46	47	55	61	85	233	1168	1150
120	46	48	53	68	97	234	1070	1150
121	46	47	57	70	106	235	1168	1150
122	46	47	49	64	90	236	1168	1150
123	46	47	55	61	85	237	1168	1150
124	46	48	50	59	83	238	1138	1150
125	46	48	50	74	108	239	1100	1150
126	46	47	51	65	92	240	1168	1150
127	46	48	58	78	112	241	959	1150
128	46	48	50	74	108	242	1103	1150
129	46	47	57	1167	103	243	1168	1150
130	46	47	51	75	109	244	1168	1150
131	46	47	57	71	105	245	1168	1150
132	46	47	54	73	107	246	1168	1150
133	46	47	54	69	100	247	1168	1150
134	46	48	52	67	99	248	1056	1150
135	46	48	50	59	83	249	1141	1150
136	46	47	51	63	89	250	1168	1150
137	46	47	57	70	1165	251	1168	1150
138	46	47	49	64	91	252	1168	1150
139	46	48	58	77	111	253	1021	1150
140	46	48	58	77	111	254	1027	1150
141	46	47	56	72	101	255	1168	1150
143	46	48	50	59	80	257	890	1150
144	46	48	53	68	96	258	938	1150
145	46	48	58	77	110	259	993	1150
146	46	48	53	68	97	260	1072	1150
147	46	48	52	60	86	261	835	1150
148	46	47	57	70	1165	262	1168	1150
149	46	47	55	79	114	263	1168	1150
150	46	48	58	62	88	264	844	1150
151	46	48	50	59	82	265	1121	1150
152	46	48	50	59	83	266	1140	1150
153	46	47	54	73	107	116	1168	43
155	46	47	49	64	90	117	1168	43
156	46	47	56	71	105	226	1168	43
157	46	47	56	1167	104	122	1168	43
159	46	48	53	68	95	121	1067	43
160	46	47	51	65	92	123	1168	43
161	46	47	51	75	109	124	1168	43
162	46	47	49	64	91	142	1168	43
164	46	47	54	69	100	127	1168	43
165	46	47	56	71	105	128	1168	43
166	46	47	54	69	100	209	1168	43
167	46	47	54	74	108	154	1092	43
170	46	47	54	74	108	132	1078	43
173	46	47	51	65	92	143	1168	43
174	46	47	49	64	90	236	1168	43
176	46	47	49	70	1165	137	1168	43
177	46	47	54	74	108	144	1086	43
179	46	47	49	76	1164	139	1168	43
180	46	47	56	1167	104	140	1168	43
181	46	47	54	69	100	145	1168	43
184	46	47	51	75	109	151	1168	43
185	46	48	53	62	88	152	842	43
186	46	48	53	78	112	153	942	43
187	46	47	54	69	100	149	1168	43
188	46	48	53	77	111	155	1001	43
192	46	47	51	63	89	158	1168	43
193	46	47	54	61	85	159	1168	43
194	46	47	56	1167	104	160	1168	43
195	46	47	54	73	107	161	1168	43
197	46	48	53	78	112	164	951	43
198	46	47	51	65	92	165	1168	43
200	46	47	56	1167	103	168	1168	43
203	46	47	51	65	92	171	1168	43
204	46	47	54	73	107	173	1168	43
205	46	47	49	70	1165	172	1168	43
207	46	47	51	65	92	175	1168	43
210	46	47	54	73	107	179	1168	43
211	46	47	49	70	1165	180	1168	43
212	46	47	49	70	1165	181	1168	43
213	46	47	49	76	1164	186	1168	43
214	46	47	51	75	109	184	1168	43
215	46	47	51	63	89	187	1168	43
216	46	47	49	64	90	185	1168	43
217	46	47	54	61	85	182	1168	43
218	46	47	49	76	1164	188	1168	43
219	46	47	54	73	107	189	1168	43
223	46	47	51	65	92	195	1168	43
225	46	48	53	68	95	199	923	43
227	46	47	54	69	100	147	1168	43
228	46	47	49	76	1164	201	1168	43
229	46	48	53	68	95	202	1059	43
230	46	48	53	77	111	198	1011	43
232	46	47	56	1167	103	245	1168	43
234	46	47	51	75	109	206	1168	43
235	46	48	53	77	110	207	971	43
238	46	47	49	64	90	212	1168	43
239	46	47	54	69	100	176	1168	43
240	46	47	56	72	101	211	1168	43
241	46	47	54	61	85	228	1168	43
242	46	47	54	73	107	225	1168	43
243	46	48	53	77	110	220	990	43
244	46	47	56	1167	104	218	1168	43
245	46	47	49	64	91	219	1168	43
248	46	47	56	72	101	222	1168	43
250	46	47	51	75	109	230	1168	43
251	46	48	53	77	111	231	1019	43
252	46	47	56	71	105	232	1168	43
253	46	47	54	61	85	233	1168	43
254	46	48	53	68	97	234	1070	43
255	46	47	56	1167	104	235	1168	43
256	46	47	54	61	85	237	1168	43
257	46	47	51	65	92	240	1168	43
258	46	47	54	74	108	239	1100	43
259	46	48	53	78	112	241	959	43
260	46	47	54	74	108	242	1103	43
261	46	47	51	75	109	244	1168	43
263	46	47	54	73	107	246	1168	43
264	46	47	54	69	100	247	1168	43
265	46	47	51	63	89	250	1168	43
266	46	47	49	64	91	252	1168	43
267	46	48	53	77	111	253	1021	43
268	46	47	56	72	101	255	1168	43
269	46	48	53	77	110	259	993	43
270	46	48	53	68	95	256	1063	43
272	46	48	53	77	111	254	1027	43
273	46	48	53	68	97	260	1072	43
274	46	47	49	76	1164	156	1168	43
275	46	47	49	70	1165	262	1168	43
278	46	47	54	73	107	173	1168	44
279	46	47	51	79	81	115	1168	44
280	46	47	56	64	90	117	1168	44
281	46	47	56	71	105	226	1168	44
282	46	47	56	1167	104	119	1168	44
283	46	47	51	79	81	120	1168	44
284	46	48	53	68	97	121	1067	44
285	46	47	51	75	109	123	1168	44
286	46	47	51	75	109	184	1168	44
287	46	47	56	64	90	125	1168	44
289	46	47	54	69	100	145	1168	44
290	46	47	56	1167	104	128	1168	44
291	46	47	54	69	100	129	1168	44
292	46	47	54	74	108	154	1092	44
294	46	47	51	79	81	205	1168	44
295	46	47	56	64	90	142	1168	44
296	46	47	54	74	108	132	1078	44
299	46	47	51	75	109	143	1168	44
300	46	47	56	64	90	135	1168	44
302	46	47	56	70	1165	137	1168	44
303	46	47	54	74	108	144	1086	44
305	46	47	56	70	1165	139	1168	44
306	46	47	56	1167	104	140	1168	44
307	46	48	53	68	96	146	913	44
308	46	48	53	68	96	148	918	44
309	46	47	51	75	109	151	1168	44
310	46	48	53	62	88	152	842	44
312	46	47	54	69	100	247	1168	44
316	46	47	51	79	81	157	1168	44
317	46	47	51	63	89	158	1168	44
318	46	47	54	61	85	182	1168	44
319	46	47	56	1167	104	183	1168	44
320	46	47	54	73	107	161	1168	44
323	46	47	51	75	109	171	1168	44
325	46	47	56	71	105	168	1168	44
331	46	47	54	73	107	246	1168	44
332	46	47	56	70	1165	180	1168	44
333	46	47	56	70	1165	181	1168	44
334	46	47	56	70	1165	186	1168	44
335	46	47	51	63	89	187	1168	44
336	46	47	56	64	90	190	1168	44
337	46	47	56	70	1165	188	1168	44
339	46	47	51	79	81	194	1168	44
341	46	47	51	75	109	195	1168	44
343	46	48	53	68	95	199	923	44
346	46	47	54	69	100	147	1168	44
347	46	47	56	70	1165	156	1168	44
348	46	48	53	68	95	202	1059	44
351	46	47	56	71	105	204	1168	44
352	46	47	51	75	109	206	1168	44
354	46	48	53	68	96	210	930	44
356	46	47	56	64	90	212	1168	44
357	46	47	54	69	100	214	1168	44
358	46	47	56	72	101	211	1168	44
359	46	47	54	61	85	228	1168	44
362	46	47	56	1167	104	218	1168	44
363	46	47	56	1167	104	217	1168	44
364	46	47	56	64	90	252	1168	44
366	46	47	54	69	100	223	1168	44
367	46	47	56	72	101	222	1168	44
369	46	48	53	68	96	229	937	44
370	46	48	53	62	88	231	1019	44
371	46	47	56	71	105	232	1168	44
372	46	47	54	61	85	233	1168	44
373	46	48	53	68	97	234	1070	44
374	46	47	56	1167	104	235	1168	44
376	46	47	54	61	85	237	1168	44
377	46	47	54	74	108	239	1100	44
379	46	47	56	71	105	245	1168	44
381	46	47	56	72	101	255	1168	44
384	46	48	53	68	95	256	1063	44
385	46	48	53	68	96	258	938	44
387	46	48	53	68	97	260	1072	44
388	46	47	51	79	81	263	1168	44
389	46	47	56	64	90	117	1168	45
390	46	48	50	60	86	130	832	45
391	46	47	56	64	90	142	1168	45
392	46	47	56	64	90	137	1168	45
393	46	47	51	63	89	151	1168	45
396	46	47	51	69	100	154	1092	45
397	46	47	51	63	89	158	1168	45
398	46	47	56	72	101	168	1168	45
399	46	47	51	69	100	173	1168	45
400	46	48	50	66	1166	178	871	45
401	46	47	56	64	90	180	1168	45
402	46	47	56	64	90	186	1168	45
404	46	47	51	69	100	147	1168	45
405	46	47	51	63	89	205	1168	45
407	46	47	56	72	101	211	1168	45
408	46	47	56	72	101	218	1168	45
409	46	47	51	69	100	223	1168	45
410	46	47	51	69	100	228	1168	45
411	46	47	56	72	101	232	1168	45

Re: Any Help

Sat, 27 Oct 2012 12:40:42 GMT

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

Gysbert_Wassenaar — Sat, 27 Oct 2012 12:45:19 GMT

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

Re: Any Help

Sat, 27 Oct 2012 12:48:30 GMT

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

Re: Any Help

Gysbert_Wassenaar — Sat, 27 Oct 2012 13:31:45 GMT

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?

Re: Any Help

Sat, 27 Oct 2012 14:13:22 GMT

Re: Any Help

Sat, 27 Oct 2012 14:14:53 GMT

Re: Any Help

Gysbert_Wassenaar — Sun, 28 Oct 2012 09:07:34 GMT

You're in luck Henric Cronström very recently wrote a blog post about this issue. You'll probably want to read his article about generic keys as well.