topic Re: Grouping Dimension in Master Items for Filter in Visualization and Usability

Grouping Dimension in Master Items for Filter

Fri, 29 Jul 2016 13:03:30 GMT

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

kumarkp412 — Fri, 29 Jul 2016 13:42:09 GMT

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

[..]

can please provide sample file.

Thanks

Kumar KVP

Re: Grouping Dimension in Master Items for Filter

Fri, 29 Jul 2016 13:47:33 GMT

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

Fri, 29 Jul 2016 13:48:22 GMT

How would I write that? And where would I put it? which script would house that?

Re: Grouping Dimension in Master Items for Filter

kumarkp412 — Fri, 29 Jul 2016 14:14:02 GMT

okay , can you explaining to me how your resultant data source should be?

you need the "parts" field which contains the merged data into the same table which will come from joining the two tables.

Re: Grouping Dimension in Master Items for Filter

Fri, 29 Jul 2016 14:16:27 GMT

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

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

Does this answer your question?

Re: Grouping Dimension in Master Items for Filter

sunny_talwar — Sun, 31 Jul 2016 18:49:35 GMT

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

Table1: