12 Replies Latest reply: Oct 28, 2012 5:09 AM by Gysbert Wassenaar RSS

    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

        • Re: Any Help
          Kaushik Solanki

          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

              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
                  Kaushik Solanki

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

                   

                  Regards,

                  Kaushik Solanki

                    • Re: Any Help

                      Hello Kaushik,

                       

                      thanks for help,

                       

                      i attached two excels please find.,

                       

                      regards,

                      baru

                      • 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
                          • Re: Any Help
                            Lav Jain

                            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

                              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
                        Gysbert Wassenaar

                        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?