6 Replies Latest reply: Jul 31, 2016 2:49 PM by Sunny Talwar RSS

    Grouping Dimension in Master Items for Filter

    Lauren Mills

      Hello,

       

      Problem - I want to be able to join two fields together that live in separate excel files to create a filter.

       

      I'm doing something wrong... my expression looks like this -

       

      if(WildMatch([Parts Advisor$.Position Code], 'Parts Advisor',), 'Parts Advisor',

      if(WildMatch([Parts Manager$.Position Code], 'Parts Manager',), 'Parts Manager','Parts Position Codes'))

       

      I want to merge the values from [Parts Advisor$.Position Code] and [Parts Manager$.Position Code] to equal a Dimension of "PARTS"

       

      The values under [Parts Advisor$.Position Code] are {14,40} and [Parts Manager$.Position Code] are {08, 32,35}.

       

      I want "PARTS" to have all values {08, 14, 32, 35, 40}.

       

      Then I want to make this a filter so be can look at those who only work with PARTS.

       

      Please help!!

        • Re: Grouping Dimension in Master Items for Filter
          kvp kumar

          Hi Lauren,

          You can use the concatenate function to merge both values from different columns into a single column.I hope two fields are coming from different tables.

          i.e

          table:

          load ..,

          Parts Advisor$.Position Code as parts

          [..]

          concatenate

          load..

          Parts Manager$.Position Code as parts

          [..]

          or

          can please provide sample file.

          Thanks

          Kumar KVP


            • Re: Grouping Dimension in Master Items for Filter
              Lauren Mills

              LOAD

                  [Dist] & '|' & [Dealer Code] as kJoinKeyService,

                  BC as BCPADV,

                  "Dealer Name"  AS [Parts Advisor$.Dealer Name],

                  F6 AS [Parts Advisor$.F6],

                  Phone AS [Parts Advisor$.Phone],

                  "Address Line 1"  AS [Parts Advisor$.Address Line 1],

                  City  AS [Parts Advisor$.City],

                  St  AS [Parts Advisor$.St],

                  "Zip Code" AS [Parts Advisor$.Zip Code],

                  Latitude  AS [Parts Advisor$.Latitude ],

                  Longitude  AS [Parts Advisor$.Longitude],

                  "Planning Potential Size"  AS [Parts Advisor$.Planning Potential Size],

                  "Sales Group Size"  AS [Parts Advisor$.Sales Group Size],

                  Franch  AS [Parts Advisor$.Franch],

                  SID  AS [Parts Advisor$.SID],

                  "First Name"  AS [Parts Advisor$.First Name],

                  "Last Name"  AS [Parts Advisor$.Last Name],

                  "Position Code" AS [Parts Advisor$.Position Code],

                  "Position Code Start Date"  AS [Parts Advisor$.Position Code Start Date],

                  Email AS [Parts Advisor$.Email],

                  Tenure AS [Parts Advisor$.Tenure],

                  "Introduction to the Diverse Customer Experience" AS [Parts Advisor$.Introduction to the Diverse Customer Experience],

                  "Customer Experience Dashboard" AS [Parts Advisor$.Customer Experience Dashboard],

                  "Putting Customers First" AS [Parts Advisor$.Putting Customers First],

                  "Maximizing Parts  Department Phone Calls" AS [Parts Advisor$.Maximizing Parts  Department Phone Calls],

                  "Welcome to FCA After Sales" AS [Parts Advisor$.Welcome to FCA After Sales],

                  "Introduction to the Learning Center" AS [Parts Advisor$.Introduction to the Learning Center],

                  "Product Fundamentals Web" AS [Parts Advisor$.Product Fundamentals Web],

                  "Recall Ready" AS [Parts Advisor$.Recall Ready],

                  "Ordering Recall Campaign Parts" AS [Parts Advisor$.Ordering Recall Campaign Parts],

                  "Warranty for Parts VCO" AS [Parts Advisor$.Warranty for Parts VCO],

                  "Introduction to DealerCONNECT for After Sales" AS [Parts Advisor$.Introduction to DealerCONNECT for After Sales],

                  "COMDASH - The Dealer Communications Portal" AS [Parts Advisor$.COMDASH - The Dealer Communications Portal],

                  "LEVEL 0 COMPLETION" AS [Parts Advisor$.LEVEL 0 COMPLETION],

                  "Jeep Wave Web Course" AS [Parts Advisor$.Jeep Wave Web Course],

                  "Parts Department Organization Web" AS [Parts Advisor$.Parts Department Organization Web],

                  "QEC Operations - Parts Returns" AS [Parts Advisor$.QEC Operations - Parts Returns],

                  "Mopar Tireworks: Tires 101" AS [Parts Advisor$.Mopar Tireworks: Tires 101],

                  "2016 Master Service and Parts Release 1" AS [Parts Advisor$.2016 Master Service and Parts Release 1],

                  "2016 Master Service and Parts Release 2" AS [Parts Advisor$.2016 Master Service and Parts Release 2],

                  "Chrysler Pacifica Launch Web Course" AS [Parts Advisor$.Chrysler Pacifica Launch Web Course],

                  "Introduction to After Sales Digital Marketing" AS [Parts Advisor$.Introduction to After Sales Digital Marketing],

                  "Social Media and Reputation Management for After Sales" AS [Parts Advisor$.Social Media and Reputation Management for After Sales],

                  "Parts Specifying Web" AS [Parts Advisor$.Parts Specifying Web],

                  "MOPAR ARO VCO" AS [Parts Advisor$.MOPAR ARO VCO],

                  "Parts Department Systems Web" AS [Parts Advisor$.Parts Department Systems Web],

                  "MOPAR eStore" AS [Parts Advisor$.MOPAR eStore],

                  "Retail Parts Selling Skills Web" AS [Parts Advisor$.Retail Parts Selling Skills Web],

                  "Mopar Collision Conquest" AS [Parts Advisor$.Mopar Collision Conquest],

                  "Chrysler Group Technologies" AS [Parts Advisor$.Chrysler Group Technologies],

                  "LEVEL 1 COMPLETION" AS [Parts Advisor$.LEVEL 1 COMPLETION],

                  "FFB - Adaptive Crusie Control" AS [Parts Advisor$.FFB - Adaptive Crusie Control],

                  "FFB - LaneSense Lane Departure Warning" AS [Parts Advisor$.FFB - LaneSense Lane Departure Warning],

                  "FFB - Uconnect 8.4 New Features" AS [Parts Advisor$.FFB - Uconnect 8.4 New Features],

                  "FFB - Phone Pairing" AS [Parts Advisor$.FFB - Phone Pairing],

                  "FFB - Universal Garage Door Opener" AS [Parts Advisor$.FFB - Universal Garage Door Opener],

                  "2016 Master Service and Parts Release 3 (WBT)" AS [Parts Advisor$.2016 Master Service and Parts Release 3 (WBT)],

                  "Performance Support for After Sales (WBT)" AS [Parts Advisor$.Performance Support for After Sales (WBT)],

                  "Intro to Electric & Hybrid Vehicles (WBT)" AS [Parts Advisor$.Intro to Electric & Hybrid Vehicles (WBT)],

                  "2016 Master Service and Part Release 4 (WBT) - Q4" AS [Parts Advisor$.2016 Master Service and Part Release 4 (WBT) - Q4],

                  "Feature Function Benefit 2 (WBT) - Q4" AS [Parts Advisor$.Feature Function Benefit 2 (WBT) - Q4],

                  "Introduction to Mobile Apps (WBT) - Q4" AS [Parts Advisor$.Introduction to Mobile Apps (WBT) - Q4],

                  "LEVEL 2 COMPLETION" AS [Parts Advisor$.LEVEL 2 COMPLETION],

                  "How to Create Raving Fans (Advocates) (VCO)" AS [Parts Advisor$.How to Create Raving Fans (Advocates) (VCO)],

                  "Merchandising MOPAR Accessories" AS [Parts Advisor$.Merchandising MOPAR Accessories],

                  "LEVEL 3 COMPLETION" AS [Parts Advisor$.LEVEL 3 COMPLETION],

                  "2016 Cert Status" AS [Parts Advisor$.2016 Cert Status],

                  [First Name]&' '&[Last Name] as [Parts Advisor Name],

                  if(match("LEVEL 0 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 0],

                  if(match("LEVEL 1 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 1],

                  if(match("LEVEL 2 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 2],

                  if(match("LEVEL 3 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 3]

              FROM [lib://Parts Advisor (cag_t1300lm)/Parts Advisor.xls]

              (biff, embedded labels, table is [Parts Advisor$]);

               

               

               

              -LOAD
                  [Dist] & '|' & [Dealer Code] as kJoinKeyService,
                  BC as BCPMAN,
                  "Dealer Name" AS [Parts Manager$.Dealer Name],
                  F6 AS [Parts Manager$.F6],
                  Phone AS [Parts Manager$.Phone],
                  "Address Line 1" AS [Parts Manager$.Address Line 1],
                  City AS [Parts Manager$.City],
                  St AS [Parts Manager$..St],
                  "Zip Code" AS [Parts Manager$.Zip Code],
                  Latitude AS [Parts Manager$.Latitude],
                  Longitude AS [Parts Manager$.Longitude],
                  "Planning Potential Size" AS [Parts Manager$.Planning Potential Size],
                  "Sales Group Size" AS [Parts Manager$.Sales Group Size],
                  Franch AS [Parts Manager$.Franch],
                  SID AS [Parts Manager$.SID],
                  "First Name" AS [Parts Manager$.First Name],
                  "Last Name" AS [Parts Manager$.Last Name],
                  "Position Code" AS [Parts Manager$.Position Code],
                  "Position Code Start Date" AS [Parts Manager$.Position Code Start Date],
                  Email AS [Parts Manager$.Email],
                  Tenure AS [Parts Manager$.Tenure ],
                  "Introduction to the Diverse Customer Experience" AS [Parts Manager$.Introduction to the Diverse Customer Experience],
                  "Customer Experience Dashboard" AS [Parts Manager$.Customer Experience Dashboard],
                  "Putting Customers First" AS [Parts Manager$.Putting Customers First],
                  "Maximizing Parts Department Phone Calls" AS [Parts Manager$.Maximizing Parts Department Phone Calls],
                  "Welcome to FCA After Sales" AS [Parts Manager$.Welcome to FCA After Sales],
                  "Introduction to the Learning Center" AS [Parts Manager$.Introduction to the Learning Center],
                  "Recall Ready" AS [Parts Manager$.Recall Ready],
                  "Ordering Recall Campaign Parts" AS [Parts Manager$.Ordering Recall Campaign Parts],
                  "Warranty for Parts" AS [Parts Manager$.Warranty for Parts],
                  "Introduction to DealerCONNECT for After Sales" AS [Parts Manager$.Introduction to DealerCONNECT for After Sales],
                  "COMDASH - The Dealer Communications Portal" AS [Parts Manager$.COMDASH - The Dealer Communications Portal],
                  "LEVEL 0 COMPLETION" AS [Parts Manager$.LEVEL 0 COMPLETION],
                  "Jeep Wave Web Course" AS [Parts Manager$.Jeep Wave Web Course],
                  "Parts Department Organization Web" AS [Parts Manager$.Parts Department Organization Web],
                  "QEC Operations - Parts Return" AS [Parts Manager$.QEC Operations - Parts Return],
                  "Mopar Tireworks: Tires 101" AS [Parts Manager$.Mopar Tireworks: Tires 101],
                  "2016 Master Service and Parts Release 1" AS [Parts Manager$.2016 Master Service and Parts Release 1],
                  "2016 Master Service and Parts Release 2" AS [Parts Manager$.2016 Master Service and Parts Release 2],
                  "Chrysler Pacifica Launch Web Course" AS [Parts Manager$.Chrysler Pacifica Launch Web Course],
                  "Introduction to After Sales Digital Marketing" AS [Parts Manager$.Introduction to After Sales Digital Marketing],
                  "Social Media and Reputation Management for After Sales" AS [Parts Manager$.Social Media and Reputation Management for After Sales],
                  "Parts Specifying Web" AS [Parts Manager$.Parts Specifying Web],
                  "Mopar ARO VCO" AS [Parts Manager$.Mopar ARO VCO],
                  "Parts Department Systems Web" AS [Parts Manager$.Parts Department Systems Web],
                  "MOPAR eStore" AS [Parts Manager$.MOPAR eStore],
                  "MRA Scrap at Dealer" AS [Parts Manager$.MRA Scrap at Dealer],
                  "Retail Parts Selling Skills Web" AS [Parts Manager$.Retail Parts Selling Skills Web],
                  "MOPAR Collision Conquest" AS [Parts Manager$.MOPAR Collision Conquest],
                  "Chrysler Group Technologies" AS [Parts Manager$.Chrysler Group Technologies],
                  "LEVEL 1 COMPLETION" AS [Parts Manager$.LEVEL 1 COMPLETION],
                  "FFB - Adaptive Crusie Control" AS [Parts Manager$.FFB - Adaptive Crusie Control],
                  "FFB - LaneSense Lane departure Warning" AS [Parts Manager$.FFB - LaneSense Lane departure Warning],
                  "FFB - Uconnect 8.4 New Features" AS [Parts Manager$.FFB - Uconnect 8.4 New Features],
                  "FFB - Phone Pairing" AS [Parts Manager$.FFB - Phone Pairing],
                  "FFB - Universal Garage Door Opener" AS [Parts Manager$.FFB - Universal Garage Door Opener],
                  "2016 Master Service and Parts Release 3 (WBT)" AS [Parts Manager$.2016 Master Service and Parts Release 3 (WBT)],
                  "Service Capacity 1: Managing Production Objectives (WBT)" AS [Parts Manager$.Service Capacity 1: Managing Production Objectives (WBT)],
                  "Performance Support for After Sales (WBT)" AS [Parts Manager$.Performance Support for After Sales (WBT)],
                  "Intro to Electric & Hybrid Vehicles (WBT)" AS [Parts Manager$.Intro to Electric & Hybrid Vehicles (WBT)],
                  "2016 Master Service and Part Release 4 (WBT) - Q4" AS [Parts Manager$.2016 Master Service and Part Release 4 (WBT) - Q4],
                  "Service Capacity Release 2 (WBT) - Q4" AS [Parts Manager$.Service Capacity Release 2 (WBT) - Q4],
                  "Feature Function Benefit 2 (WBT) - Q4" AS [Parts Manager$.Feature Function Benefit 2 (WBT) - Q4],
                  "Service Capacity Release 3 (WBT) - Q4" AS [Parts Manager$.Service Capacity Release 3 (WBT) - Q4],
                  "Introduction to Mobile Apps (WBT) - Q4" AS [Parts Manager$.Introduction to Mobile Apps (WBT) - Q4],
                  "LEVEL 2 COMPLETION" AS [Parts Manager$.LEVEL 2 COMPLETION],
                  "iEXAM - Financial Analysis for Parts (VCO)" AS [Parts Manager$.iEXAM - Financial Analysis for Parts (VCO)],
                  "How to Create Raving Fans (Advocates) (VCO)" AS [Parts Manager$.How to Create Raving Fans (Advocates) (VCO)],
                  "Merchandising MOPAR Accessories" AS [Parts Manager$.Merchandising MOPAR Accessories],
                  "Applied Coaching Techniques (VCO)" AS [Parts Manager$.Applied Coaching Techniques (VCO)],
                  "How To Hire and Retain Great Employees (VCO)" AS [Parts Manager$.How To Hire and Retain Great Employees (VCO)],
                  "LEVEL 3 COMPLETION" AS [Parts Manager$.LEVEL 3 COMPLETION],
                  "2016 Cert Status" AS [Parts Manager$.2016 Cert Status],
                  if(match("LEVEL 0 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 0],
                  if(match("LEVEL 1 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 1],
                  if(match("LEVEL 2 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 2],
                  if(match("LEVEL 3 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 3]
              FROM [lib://Parts Manager (cag_t1300lm)/Parts Manager.xls]
              (biff, embedded labels, table is [Parts Manager$]);

            • Re: Grouping Dimension in Master Items for Filter
              Sunny Talwar

              How about creating a Linktable to do this. Assuming kJoinKeyService is the link between the two tables (Changes highlighted in different colors

               

              Table1:

              LOAD

                  [Dist] & '|' & [Dealer Code] as kJoinKeyService,

                  BC as BCPADV,

                  "Dealer Name"  AS [Parts Advisor$.Dealer Name],

                  F6 AS [Parts Advisor$.F6],

                  Phone AS [Parts Advisor$.Phone],

                  "Address Line 1"  AS [Parts Advisor$.Address Line 1],

                  City  AS [Parts Advisor$.City],

                  St  AS [Parts Advisor$.St],

                  "Zip Code" AS [Parts Advisor$.Zip Code],

                  Latitude  AS [Parts Advisor$.Latitude ],

                  Longitude  AS [Parts Advisor$.Longitude],

                  "Planning Potential Size"  AS [Parts Advisor$.Planning Potential Size],

                  "Sales Group Size"  AS [Parts Advisor$.Sales Group Size],

                  Franch  AS [Parts Advisor$.Franch],

                  SID  AS [Parts Advisor$.SID],

                  "First Name"  AS [Parts Advisor$.First Name],

                  "Last Name"  AS [Parts Advisor$.Last Name],

                  "Position Code" AS [Parts Advisor$.Position Code],

                  "Position Code Start Date"  AS [Parts Advisor$.Position Code Start Date],

                  Email AS [Parts Advisor$.Email],

                  Tenure AS [Parts Advisor$.Tenure],

                  "Introduction to the Diverse Customer Experience" AS [Parts Advisor$.Introduction to the Diverse Customer Experience],

                  "Customer Experience Dashboard" AS [Parts Advisor$.Customer Experience Dashboard],

                  "Putting Customers First" AS [Parts Advisor$.Putting Customers First],

                  "Maximizing Parts  Department Phone Calls" AS [Parts Advisor$.Maximizing Parts  Department Phone Calls],

                  "Welcome to FCA After Sales" AS [Parts Advisor$.Welcome to FCA After Sales],

                  "Introduction to the Learning Center" AS [Parts Advisor$.Introduction to the Learning Center],

                  "Product Fundamentals Web" AS [Parts Advisor$.Product Fundamentals Web],

                  "Recall Ready" AS [Parts Advisor$.Recall Ready],

                  "Ordering Recall Campaign Parts" AS [Parts Advisor$.Ordering Recall Campaign Parts],

                  "Warranty for Parts VCO" AS [Parts Advisor$.Warranty for Parts VCO],

                  "Introduction to DealerCONNECT for After Sales" AS [Parts Advisor$.Introduction to DealerCONNECT for After Sales],

                  "COMDASH - The Dealer Communications Portal" AS [Parts Advisor$.COMDASH - The Dealer Communications Portal],

                  "LEVEL 0 COMPLETION" AS [Parts Advisor$.LEVEL 0 COMPLETION],

                  "Jeep Wave Web Course" AS [Parts Advisor$.Jeep Wave Web Course],

                  "Parts Department Organization Web" AS [Parts Advisor$.Parts Department Organization Web],

                  "QEC Operations - Parts Returns" AS [Parts Advisor$.QEC Operations - Parts Returns],

                  "Mopar Tireworks: Tires 101" AS [Parts Advisor$.Mopar Tireworks: Tires 101],

                  "2016 Master Service and Parts Release 1" AS [Parts Advisor$.2016 Master Service and Parts Release 1],

                  "2016 Master Service and Parts Release 2" AS [Parts Advisor$.2016 Master Service and Parts Release 2],

                  "Chrysler Pacifica Launch Web Course" AS [Parts Advisor$.Chrysler Pacifica Launch Web Course],

                  "Introduction to After Sales Digital Marketing" AS [Parts Advisor$.Introduction to After Sales Digital Marketing],

                  "Social Media and Reputation Management for After Sales" AS [Parts Advisor$.Social Media and Reputation Management for After Sales],

                  "Parts Specifying Web" AS [Parts Advisor$.Parts Specifying Web],

                  "MOPAR ARO VCO" AS [Parts Advisor$.MOPAR ARO VCO],

                  "Parts Department Systems Web" AS [Parts Advisor$.Parts Department Systems Web],

                  "MOPAR eStore" AS [Parts Advisor$.MOPAR eStore],

                  "Retail Parts Selling Skills Web" AS [Parts Advisor$.Retail Parts Selling Skills Web],

                  "Mopar Collision Conquest" AS [Parts Advisor$.Mopar Collision Conquest],

                  "Chrysler Group Technologies" AS [Parts Advisor$.Chrysler Group Technologies],

                  "LEVEL 1 COMPLETION" AS [Parts Advisor$.LEVEL 1 COMPLETION],

                  "FFB - Adaptive Crusie Control" AS [Parts Advisor$.FFB - Adaptive Crusie Control],

                  "FFB - LaneSense Lane Departure Warning" AS [Parts Advisor$.FFB - LaneSense Lane Departure Warning],

                  "FFB - Uconnect 8.4 New Features" AS [Parts Advisor$.FFB - Uconnect 8.4 New Features],

                  "FFB - Phone Pairing" AS [Parts Advisor$.FFB - Phone Pairing],

                  "FFB - Universal Garage Door Opener" AS [Parts Advisor$.FFB - Universal Garage Door Opener],

                  "2016 Master Service and Parts Release 3 (WBT)" AS [Parts Advisor$.2016 Master Service and Parts Release 3 (WBT)],

                  "Performance Support for After Sales (WBT)" AS [Parts Advisor$.Performance Support for After Sales (WBT)],

                  "Intro to Electric & Hybrid Vehicles (WBT)" AS [Parts Advisor$.Intro to Electric & Hybrid Vehicles (WBT)],

                  "2016 Master Service and Part Release 4 (WBT) - Q4" AS [Parts Advisor$.2016 Master Service and Part Release 4 (WBT) - Q4],

                  "Feature Function Benefit 2 (WBT) - Q4" AS [Parts Advisor$.Feature Function Benefit 2 (WBT) - Q4],

                  "Introduction to Mobile Apps (WBT) - Q4" AS [Parts Advisor$.Introduction to Mobile Apps (WBT) - Q4],

                  "LEVEL 2 COMPLETION" AS [Parts Advisor$.LEVEL 2 COMPLETION],

                  "How to Create Raving Fans (Advocates) (VCO)" AS [Parts Advisor$.How to Create Raving Fans (Advocates) (VCO)],

                  "Merchandising MOPAR Accessories" AS [Parts Advisor$.Merchandising MOPAR Accessories],

                  "LEVEL 3 COMPLETION" AS [Parts Advisor$.LEVEL 3 COMPLETION],

                  "2016 Cert Status" AS [Parts Advisor$.2016 Cert Status],

                  [First Name]&' '&[Last Name] as [Parts Advisor Name],

                  if(match("LEVEL 0 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 0],

                  if(match("LEVEL 1 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 1],

                  if(match("LEVEL 2 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 2],

                  if(match("LEVEL 3 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS ADVISOR LEVEL 3]

              FROM [lib://Parts Advisor (cag_t1300lm)/Parts Advisor.xls]

              (biff, embedded labels, table is [Parts Advisor$]);

               

              Table2:

              -LOAD
                  [Dist] & '|' & [Dealer Code] as kJoinKeyService,
                  BC as BCPMAN,
                  "Dealer Name" AS [Parts Manager$.Dealer Name],
                  F6 AS [Parts Manager$.F6],
                  Phone AS [Parts Manager$.Phone],
                  "Address Line 1" AS [Parts Manager$.Address Line 1],
                  City AS [Parts Manager$.City],
                  St AS [Parts Manager$..St],
                  "Zip Code" AS [Parts Manager$.Zip Code],
                  Latitude AS [Parts Manager$.Latitude],
                  Longitude AS [Parts Manager$.Longitude],
                  "Planning Potential Size" AS [Parts Manager$.Planning Potential Size],
                  "Sales Group Size" AS [Parts Manager$.Sales Group Size],
                  Franch AS [Parts Manager$.Franch],
                  SID AS [Parts Manager$.SID],
                  "First Name" AS [Parts Manager$.First Name],
                  "Last Name" AS [Parts Manager$.Last Name],
                  "Position Code" AS [Parts Manager$.Position Code],
                  "Position Code Start Date" AS [Parts Manager$.Position Code Start Date],
                  Email AS [Parts Manager$.Email],
                  Tenure AS [Parts Manager$.Tenure ],
                  "Introduction to the Diverse Customer Experience" AS [Parts Manager$.Introduction to the Diverse Customer Experience],
                  "Customer Experience Dashboard" AS [Parts Manager$.Customer Experience Dashboard],
                  "Putting Customers First" AS [Parts Manager$.Putting Customers First],
                  "Maximizing Parts Department Phone Calls" AS [Parts Manager$.Maximizing Parts Department Phone Calls],
                  "Welcome to FCA After Sales" AS [Parts Manager$.Welcome to FCA After Sales],
                  "Introduction to the Learning Center" AS [Parts Manager$.Introduction to the Learning Center],
                  "Recall Ready" AS [Parts Manager$.Recall Ready],
                  "Ordering Recall Campaign Parts" AS [Parts Manager$.Ordering Recall Campaign Parts],
                  "Warranty for Parts" AS [Parts Manager$.Warranty for Parts],
                  "Introduction to DealerCONNECT for After Sales" AS [Parts Manager$.Introduction to DealerCONNECT for After Sales],
                  "COMDASH - The Dealer Communications Portal" AS [Parts Manager$.COMDASH - The Dealer Communications Portal],
                  "LEVEL 0 COMPLETION" AS [Parts Manager$.LEVEL 0 COMPLETION],
                  "Jeep Wave Web Course" AS [Parts Manager$.Jeep Wave Web Course],
                  "Parts Department Organization Web" AS [Parts Manager$.Parts Department Organization Web],
                  "QEC Operations - Parts Return" AS [Parts Manager$.QEC Operations - Parts Return],
                  "Mopar Tireworks: Tires 101" AS [Parts Manager$.Mopar Tireworks: Tires 101],
                  "2016 Master Service and Parts Release 1" AS [Parts Manager$.2016 Master Service and Parts Release 1],
                  "2016 Master Service and Parts Release 2" AS [Parts Manager$.2016 Master Service and Parts Release 2],
                  "Chrysler Pacifica Launch Web Course" AS [Parts Manager$.Chrysler Pacifica Launch Web Course],
                  "Introduction to After Sales Digital Marketing" AS [Parts Manager$.Introduction to After Sales Digital Marketing],
                  "Social Media and Reputation Management for After Sales" AS [Parts Manager$.Social Media and Reputation Management for After Sales],
                  "Parts Specifying Web" AS [Parts Manager$.Parts Specifying Web],
                  "Mopar ARO VCO" AS [Parts Manager$.Mopar ARO VCO],
                  "Parts Department Systems Web" AS [Parts Manager$.Parts Department Systems Web],
                  "MOPAR eStore" AS [Parts Manager$.MOPAR eStore],
                  "MRA Scrap at Dealer" AS [Parts Manager$.MRA Scrap at Dealer],
                  "Retail Parts Selling Skills Web" AS [Parts Manager$.Retail Parts Selling Skills Web],
                  "MOPAR Collision Conquest" AS [Parts Manager$.MOPAR Collision Conquest],
                  "Chrysler Group Technologies" AS [Parts Manager$.Chrysler Group Technologies],
                  "LEVEL 1 COMPLETION" AS [Parts Manager$.LEVEL 1 COMPLETION],
                  "FFB - Adaptive Crusie Control" AS [Parts Manager$.FFB - Adaptive Crusie Control],
                  "FFB - LaneSense Lane departure Warning" AS [Parts Manager$.FFB - LaneSense Lane departure Warning],
                  "FFB - Uconnect 8.4 New Features" AS [Parts Manager$.FFB - Uconnect 8.4 New Features],
                  "FFB - Phone Pairing" AS [Parts Manager$.FFB - Phone Pairing],
                  "FFB - Universal Garage Door Opener" AS [Parts Manager$.FFB - Universal Garage Door Opener],
                  "2016 Master Service and Parts Release 3 (WBT)" AS [Parts Manager$.2016 Master Service and Parts Release 3 (WBT)],
                  "Service Capacity 1: Managing Production Objectives (WBT)" AS [Parts Manager$.Service Capacity 1: Managing Production Objectives (WBT)],
                  "Performance Support for After Sales (WBT)" AS [Parts Manager$.Performance Support for After Sales (WBT)],
                  "Intro to Electric & Hybrid Vehicles (WBT)" AS [Parts Manager$.Intro to Electric & Hybrid Vehicles (WBT)],
                  "2016 Master Service and Part Release 4 (WBT) - Q4" AS [Parts Manager$.2016 Master Service and Part Release 4 (WBT) - Q4],
                  "Service Capacity Release 2 (WBT) - Q4" AS [Parts Manager$.Service Capacity Release 2 (WBT) - Q4],
                  "Feature Function Benefit 2 (WBT) - Q4" AS [Parts Manager$.Feature Function Benefit 2 (WBT) - Q4],
                  "Service Capacity Release 3 (WBT) - Q4" AS [Parts Manager$.Service Capacity Release 3 (WBT) - Q4],
                  "Introduction to Mobile Apps (WBT) - Q4" AS [Parts Manager$.Introduction to Mobile Apps (WBT) - Q4],
                  "LEVEL 2 COMPLETION" AS [Parts Manager$.LEVEL 2 COMPLETION],
                  "iEXAM - Financial Analysis for Parts (VCO)" AS [Parts Manager$.iEXAM - Financial Analysis for Parts (VCO)],
                  "How to Create Raving Fans (Advocates) (VCO)" AS [Parts Manager$.How to Create Raving Fans (Advocates) (VCO)],
                  "Merchandising MOPAR Accessories" AS [Parts Manager$.Merchandising MOPAR Accessories],
                  "Applied Coaching Techniques (VCO)" AS [Parts Manager$.Applied Coaching Techniques (VCO)],
                  "How To Hire and Retain Great Employees (VCO)" AS [Parts Manager$.How To Hire and Retain Great Employees (VCO)],
                  "LEVEL 3 COMPLETION" AS [Parts Manager$.LEVEL 3 COMPLETION],
                  "2016 Cert Status" AS [Parts Manager$.2016 Cert Status],
                  if(match("LEVEL 0 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 0],
                  if(match("LEVEL 1 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 1],
                  if(match("LEVEL 2 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 2],
                  if(match("LEVEL 3 COMPLETION",'N','L','S'),[First Name]&' '&[Last Name]) as [PARTS MANAGER LEVEL 3]
              FROM [lib://Parts Manager (cag_t1300lm)/Parts Manager.xls]
              (biff, embedded labels, table is [Parts Manager$]);

               

              LinkTable:

              LOAD kJoinKeyService,

                        [Parts Advisor$.Position Code] as Parts,

                        'Table1: Advisor' as TableFlag

              Resident Table1;

               

              Concatenate (LinkTable)

              LOAD kJoinKeyService,

                        [Parts Manager$.Position Code] as Parts,

                        'Table2: Manager' as TableFlag

              Resident Table2;