Solved: Re: Qlik Sense KML File Expression issue? - Qlik Community

ishansjain2095 · ‎2020-01-17

Hi All,

Facing Issue in my Qlik Sense Hub i.e. not able to map .name col values and .area col values from XML codes shown below:

CODE:

<?xml version="1.0" encoding="utf-8" ?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document id="root_doc">
<Schema name="IND_DIST" id="gadm36_IND_2">
<SimpleField name="NAME_0" type="string"></SimpleField>
<SimpleField name="NAME_1" type="string"></SimpleField>
<SimpleField name="NAME_2" type="string"></SimpleField>
</Schema>
<Folder><name>gadm36_IND_2</name>
<Placemark>
<Style><LineStyle><color>ff0000ff</color></LineStyle><PolyStyle><fill>0</fill></PolyStyle></Style>
<ExtendedData><SchemaData schemaUrl="#gadm36_IND_2">
<SimpleData name="NAME_0">India</SimpleData>
<SimpleData name="NAME_1">Andaman and Nicobar</SimpleData>
<SimpleData name="NAME_2">Nicobar Islands</SimpleData>
</SchemaData></ExtendedData>

etc.

And sharing you guys some of my screenshots in zip file.

Regards,

Ishan

Software Engineer

lorenzoconforti · ‎2020-01-19

Can you actually manipulate the KML file or do you need to import it as is? The problem is that the name you want to link your area to is in an attribute of the simple data field rather than in a name field itself (which is what you would need).

So, unless you can modify the KML file, you would need to import it first as an XML to extract the name and then join it back to the are coordinates (extracted by importing the file as KML)

See dashboard attached

View solution in original post

dmoon2208 · ‎2020-01-19

HI

I have attached the corrected files

The definition for the place in the KML File needs to look like:

<Placemark>
<Style><LineStyle><color>ff0000ff</color></LineStyle><PolyStyle><fill>0</fill></PolyStyle></Style>
<ExtendedData><SchemaData schemaUrl="#india">
<name>Nicobar Islands</name>
</SchemaData></ExtendedData>

If you want the state and country you then create a csv file that looks like :

NAME_0	NAME_1	NAME_2
India	Andaman and Nicobar	Nicobar Islands
India	Andaman and Nicobar	North and Middle Andaman
India	Andaman and Nicobar	South Andaman
India	Andhra Pradesh	Anantapur
India	Andhra Pradesh	Chittoor
India	Andhra Pradesh	East Godavari

and you associate name in the kml file with NAME_2 in the CSV and you end up with

lorenzoconforti · ‎2020-01-19

Can you actually manipulate the KML file or do you need to import it as is? The problem is that the name you want to link your area to is in an attribute of the simple data field rather than in a name field itself (which is what you would need).

So, unless you can modify the KML file, you would need to import it first as an XML to extract the name and then join it back to the are coordinates (extracted by importing the file as KML)

See dashboard attached

ishansjain2095 · ‎2020-01-20

Hi Dmoon2208,

Thanks for the solution btw how did you converted csv to kml file?

Regards,

Ishan

ishansjain2095 · ‎2020-01-20

Hi Lorenzoconforti,

Thanks for the solution. Can you share me the kml file that you attached in IndiaKML.qvf?

Regards,

Ishan

lorenzoconforti · ‎2020-01-20

It's the same you uploaded, I haven't made any changes

ishansjain2095 · ‎2020-01-20

Hi Lorenzoconforti,

How did you extract this code witten below:

RandomData:
Load * Inline [
NAME_2, RandomData
Adilabad, 726
Agar Malwa, 511
Agra, 688
Ahmadabad, 137
Ahmadnagar, 218
Aizawl, 682
Ajmer, 90
Akola, 529
Alappuzha, 761
Aligarh, 623
Alipurduar, 285
Alirajpur, 444
Allahabad, 525
Almora, 435
Alwar, 443
Ambala, 209
Ambedkar Nagar, 234
Amethi, 107
Amravati, 105
Amreli, 651
Amritsar, 620
Amroha, 255
Anand, 607
Anantapur, 675
Anantnag, 690
Anjaw, 433
Anugul, 837
Anuppur, 911
Araria, 38
Aravalli, 955
Ariyalur, 560
Arwal, 883
Ashoknagar, 706
Auraiya, 917
Aurangabad, 397
Azamgarh, 953
Badgam, 910
Bagalkot, 94
Bageshwar, 611
Baghpat, 797
Bahraich, 11
Baksa, 296
Balaghat, 818
Balangir, 216
Baleshwar, 267
Ballia, 93
Balod, 444
Baloda Bazar, 593
Balrampur, 960
Banas Kantha, 401
Banda, 14
Bandipore, 981
Bangalore, 68
Bangalore Rural, 643
Banka, 625
Bankura, 127
Banswara, 661
Barabanki, 425
Baramulla, 399
Baran, 465
Barddhaman, 5
Bareilly, 83
Bargarh, 83
Barmer, 603
Barnala, 80
Barpeta, 477
Barwani, 268
Bastar, 293
Basti, 996
Bathinda, 590
Bauda, 661
Begusarai, 243
Belgaum, 35
Bellary, 566
Bemetara, 336
Betul, 162
Bhadrak, 692
Bhagalpur, 971
Bhandara, 386
Bharatpur, 895
Bharuch, 208
Bhavnagar, 386
Bhilwara, 238
Bhind, 221
Bhiwani, 168
Bhojpur, 582
Bhopal, 663
Bid, 372
Bidar, 717
Bijapur, 949
Bijnor, 349
Bikaner, 662
Bilaspur, 561
Birbhum, 956
Bishnupur, 442
Bokaro, 660
Bongaigaon, 56
Botad, 770
Budaun, 326
Bulandshahr, 382
Buldana, 872
Bundi, 425
Burhanpur, 874
Buxar, 114
Cachar, 867
Chamba, 150
Chamoli, 259
Champawat, 272
Champhai, 806
Chamrajnagar, 214
Chandauli, 813
Chandel, 715
Chandigarh, 444
Chandrapur, 866
Changlang, 342
Chatra, 762
Chennai, 318
Chhatarpur, 917
Chhindwara, 908
Chhota Udaipur, 675
Chikballapura, 830
Chikmagalur, 93
Chirang, 328
Chitradurga, 245
Chitrakoot, 302
Chittaurgarh, 807
Chittoor, 642
Churachandpur, 71
Churu, 22
Coimbatore, 266
Cuddalore, 793
Cuttack, 162
Dadra and Nagar Haveli, 687
Dahod, 467
Dakshin Dinajpur, 327
Dakshina Kannada, 272
Daman, 13
Damoh, 197
Dantewada, 394
Darbhanga, 252
Darjiling, 689
Darrang, 133
Datia, 901
Dausa, 851
Davanagere, 332
Debagarh, 782
Dehradun, 352
Deoghar, 822
Deoria, 567
Devbhumi Dwarka, 239
Dewas, 377
Dhalai, 65
Dhamtari, 662
Dhanbad, 903
Dhar, 904
Dharmapuri, 35
Dharwad, 759
Dhaulpur, 461
Dhemaji, 393
Dhenkanal, 586
Dhubri, 613
Dhule, 348
Dibang Valley, 936
Dibrugarh, 254
Dima Hasao, 714
Dimapur, 3
Dindigul, 280
Dindori, 1
Diu, 671
Doda, 407
Dumka, 446
Dungarpur, 436
Durg, 711
East Garo Hills, 100
East Godavari, 30
East Kameng, 221
East Khasi Hills, 306
East Nimar, 463
East Siang, 713
East Sikkim, 173
Ernakulam, 942
Erode, 329
Etah, 26
Etawah, 291
Faizabad, 795
Faridabad, 375
Faridkot, 495
Farrukhabad, 967
Fatehabad, 759
Fatehgarh Sahib, 724
Fatehpur, 987
Fazilka, 937
Firozabad, 129
Firozpur, 485
Gadag, 491
Gajapati, 192
Ganderbal, 307
Gandhinagar, 352
Ganganagar, 467
Ganjam, 990
Garhchiroli, 723
Garhwa, 914
Garhwal, 490
Gariaband, 344
Gautam Buddha Nagar, 967
Gaya, 181
Ghaziabad, 21
Ghazipur, 995
Gir Somnath, 355
Giridih, 978
Goalpara, 721
Godda, 501
Golaghat, 457
Gomati, 82
Gonda, 18
Gondiya, 103
Gopalganj, 346
Gorakhpur, 86
Gulbarga, 61
Gumla, 692
Guna, 813
Guntur, 328
Gurdaspur, 841
Gurgaon, 965
Gwalior, 932
Hailakandi, 773
Hamirpur, 694
Hanumangarh, 491
Haora, 342
Hapur, 48
Harda, 119
Hardoi, 76
Hardwar, 829
Hassan, 48
Hathras, 209
Haveri, 898
Hazaribagh, 623
Hingoli, 532
Hisar, 160
Hoshangabad, 336
Hoshiarpur, 566
Hugli, 745
Hyderabad, 741
Idukki, 1
Imphal East, 873
Imphal West, 205
Indore, 473
Jabalpur, 192
Jagatsinghapur, 303
Jaintia Hills, 438
Jaipur, 894
Jaisalmer, 469
Jajapur, 16
Jalandhar, 505
Jalaun, 792
Jalgaon, 565
Jalna, 781
Jalor, 489
Jalpaiguri, 243
Jammu, 349
Jamnagar, 647
Jamtara, 670
Jamui, 128
Janjgir-Champa, 477
Jashpur, 139
Jaunpur, 941
Jehanabad, 516
Jhabua, 91
Jhajjar, 990
Jhalawar, 951
Jhansi, 197
Jharsuguda, 911
Jhunjhunun, 474
Jind, 430
Jodhpur, 591
Jorhat, 839
Junagadh, 746
Kabeerdham, 625
Kachchh, 446
Kaimur, 96
Kaithal, 211
Kalahandi, 670
Kamrup, 293
Kamrup Metropolitan, 233
Kancheepuram, 41
Kandhamal, 295
Kangra, 937
Kannauj, 641
Kanniyakumari, 216
Kannur, 124
Kanpur Dehat, 771
Kanpur Nagar, 460
Kapurthala, 120
Karaikal, 185
Karauli, 792
Karbi Anglong, 845
Kargil, 144
Karimganj, 885
Karimnagar, 986
Karnal, 259
Karur, 615
Kasaragod, 787
Kasganj, 272
Kathua, 952
Katihar, 499
Katni, 109
Kaushambi, 90
Kendrapara, 595
Kendujhar, 204
Khagaria, 390
Khammam, 673
Kheda, 791
Khordha, 230
Khowai, 968
Khunti, 241
Kinnaur, 609
Kiphire, 420
Kishanganj, 237
Kishtwar, 550
Koch Bihar, 942
Kodagu, 442
Kodarma, 539
Kohima, 583
Kokrajhar, 190
Kolar, 669
Kolasib, 536
Kolhapur, 940
Kolkata, 726
Kollam, 204
Kondagaon, 231
Koppal, 61
Koraput, 187
Korba, 827
Koriya, 784
Kota, 342
Kottayam, 858
Kozhikode, 224
Krishna, 555
Krishnagiri, 520
Kulgam, 55
Kullu, 353
Kupwara, 990
Kurnool, 393
Kurukshetra, 203
Kurung Kumey, 649
Kushinagar, 947
Lahul & Spiti, 446
Lakhimpur, 428
Lakhimpur Kheri, 264
Lakhisarai, 602
Lakshadweep, 459
Lalitpur, 904
Latehar, 947
Latur, 593
Lawangtlai, 880
Leh (Ladakh), 729
Lohardaga, 921
Lohit, 294
Longding, 952
Longleng, 151
Lower Dibang Valley, 68
Lower Subansiri, 518
Lucknow, 131
Ludhiana, 427
Lunglei, 472
Madhepura, 763
Madhubani, 481
Madurai, 865
Maharajganj, 319
Mahasamund, 796
Mahbubnagar, 563
Mahe, 93
Mahendragarh, 819
Mahesana, 672
Mahisagar, 218
Mahoba, 158
Mainpuri, 929
Malappuram, 539
Maldah, 380
Malkangiri, 980
Mamit, 675
Mandi, 330
Mandla, 488
Mandsaur, 43
Mandya, 592
Mansa, 66
Mathura, 867
Mau, 159
Mayurbhanj, 306
Medak, 582
Meerut, 375
Mewat, 13
Mirzapur, 652
Moga, 157
Mokokchung, 110
Mon, 783
Moradabad, 656
Morbi, 46
Morena, 434
Morigaon, 722
Muktsar, 772
Mumbai City, 800
Mumbai Suburban, 33
Mungeli, 788
Munger, 626
Murshidabad, 651
Muzaffarnagar, 792
Muzaffarpur, 238
Mysore, 7
Nabarangapur, 6
Nadia, 611
Nagaon, 570
Nagappattinam, 831
Nagaur, 779
Nagpur, 148
Nainital, 970
Nalanda, 67
Nalbari, 820
Nalgonda, 923
Namakkal, 229
Namsai, 442
Nanded, 519
Nandurbar, 997
Narayanpur, 921
Narmada, 806
Narsimhapur, 415
Nashik, 998
Navsari, 340
Nawada, 142
Nayagarh, 59
Neemuch, 781
Nellore, 740
Nicobar Islands, 635
Nizamabad, 223
North 24 Parganas, 531
North and Middle Andaman, 417
North Garo Hills, 559
North Goa, 599
North Sikkim, 174
North Tripura, 290
Nuapada, 839
Osmanabad, 258
Pakur, 230
Palakkad, 502
Palamu, 428
Palghar, 26
Pali, 109
Palwal, 882
Panch Mahals, 167
Panchkula, 776
Panipat, 888
Panna, 828
Papum Pare, 457
Parbhani, 543
Pashchim Champaran, 402
Pashchim Medinipur, 894
Pashchimi Singhbhum, 596
Patan, 354
Pathanamthitta, 884
Pathankot, 880
Patiala, 981
Patna, 458
Perambalur, 596
Peren, 367
Phek, 995
Pilibhit, 456
Pithoragarh, 973
Poonch, 873
Porbandar, 529
Prakasam, 663
Pratapgarh, 943
Puducherry, 586
Pudukkottai, 7
Pulwama, 145
Pune, 332
Purba Champaran, 871
Purba Medinipur, 882
Purbi Singhbhum, 464
Puri, 912
Purnia, 67
Puruliya, 195
Rae Bareli, 729
Raichur, 479
Raigarh, 497
Raipur, 70
Raisen, 45
Rajgarh, 233
Rajkot, 664
Rajnandgaon, 625
Rajouri, 796
Rajsamand, 301
Ramanagara, 153
Ramanathapuram, 224
Ramban, 653
Ramgarh, 894
Rampur, 289
Ranchi, 623
Ranga Reddy, 879
Ratlam, 49
Ratnagiri, 562
Rayagada, 298
Reasi, 56
Rewa, 690
Rewari, 780
Ri Bhoi, 192
Rohtak, 692
Rohtas, 329
Rudraprayag, 560
Rupnagar, 313
Sabar Kantha, 946
Sagar, 100
Saharanpur, 339
Saharsa, 214
Sahibganj, 171
Sahibzada Ajit Singh Nagar, 466
Saiha, 180
Salem, 448
Samastipur, 22
Samba, 442
Sambalpur, 108
Sambhal, 751
Sangli, 791
Sangrur, 148
Sant Kabir Nagar, 155
Sant Ravi Das Nagar, 984
Saraikela-kharsawan, 22
Saran, 575
Satara, 447
Satna, 97
Sawai Madhopur, 126
Sehore, 999
Senapati, 670
Seoni, 123
Serchhip, 70
Shahdol, 544
Shahid Bhagat Singh Nagar, 889
Shahjahanpur, 998
Shajapur, 419
Shamli, 226
Sheikhpura, 338
Sheohar, 125
Sheopur, 770
Shimla, 285
Shimoga, 529
Shivpuri, 485
Shravasti, 12
Shupiyan, 862
Siddharth Nagar, 657
Sidhi, 517
Sikar, 564
Simdega, 814
Sindhudurg, 499
Singrauli, 387
Sipahijala, 599
Sirmaur, 302
Sirohi, 643
Sirsa, 816
Sitamarhi, 863
Sitapur, 50
Sivaganga, 494
Sivasagar, 854
Siwan, 271
Solan, 825
Solapur, 658
Sonbhadra, 284
Sonipat, 515
Sonitpur, 312
South 24 Parganas, 628
South Andaman, 923
South Garo Hills, 800
South Goa, 49
South Sikkim, 674
South Tripura, 557
South West Garo Hills, 341
South West Khasi Hills, 553
Srikakulam, 25
Srinagar, 75
Subarnapur, 647
Sukma, 651
Sultanpur, 327
Sundargarh, 642
Supaul, 965
Surajpur, 39
Surat, 422
Surendranagar, 643
Surguja, 454
Tamenglong, 740
Tapi, 801
Tarn Taran, 273
Tawang, 471
Tehri Garhwal, 562
Thane, 776
Thanjavur, 928
The Dangs, 336
The Nilgiris, 49
Theni, 45
Thiruvallur, 31
Thiruvananthapuram, 264
Thiruvarur, 860
Thoothukkudi, 366
Thoubal, 707
Thrissur, 403
Tikamgarh, 235
Tinsukia, 281
Tirap, 656
Tiruchirappalli, 424
Tirunelveli, 572
Tiruppur, 192
Tiruvannamalai, 897
Tonk, 461
Tuensang, 228
Tumkur, 714
Udaipur, 168
Udalguri, 848
Udham Singh Nagar, 162
Udhampur, 504
Udupi, 974
Ujjain, 800
Ukhrul, 952
Umaria, 948
Una, 621
Unnao, 341
Unokoti, 495
Upper Siang, 984
Upper Subansiri, 514
Uttar Bastar Kanker, 666
Uttar Dinajpur, 83
Uttara Kannada, 164
Uttarkashi, 73
Vadodara, 822
Vaishali, 172
Valsad, 838
Varanasi, 673
Vellore, 7
Vidisha, 137
Viluppuram, 78
Virudunagar, 851
Visakhapatnam, 237
Vizianagaram, 461
Warangal, 165
Wardha, 498
Washim, 863
Wayanad, 600
West, 116
West Garo Hills, 657
West Godavari, 804
West Kameng, 695
West Khasi Hills, 429
West Nimar, 381
West Siang, 17
West Sikkim, 646
West Tripura, 350
Wokha, 33
Y.S.R., 294
Yadgir, 901
Yamunanagar, 64
Yanam, 898
Yavatmal, 687
Zunheboto, 75

];

ishansjain2095 · ‎2020-01-20

Hi Lorenzoconforti,

Sorry for the trouble I didn't saw the entire script and thanks once again for helping me.

Regards,

Ishan

ishansjain2095 · ‎2020-01-20

Hi Lorenzoconforti,

Can you please share me the xml that you created.

lorenzoconforti · ‎2020-01-20

I haven't created any XML, just imported your file twice, first as an XML and then as a KML. This is the code in the app

IndiaMaps:
LOAD
'p'&RowNo() as NameID,
SimpleData%Table as NAME_2
FROM [lib://India/IND_DIST.kml] (XmlSimple, Table is [kml/Document/Folder/Placemark/ExtendedData/SchemaData/SimpleData]) where name = 'NAME_2';
Left Join LOAD IND_DIST.Name as NameID,
IND_DIST.Area
FROM
[lib://India/IND_DIST.kml]
(kml);

drop field NameID from IndiaMaps;