Bar Graph Question - Duplicated Dimenison Fields

[msawyercke]

I have a bar graph that uses addresses as the Dimension along the x-axis, as follows:

And here is the Dimension screenshot:

However, there is another field in the file I would like to use instead of the address field.  It is a "Name" field, but the problem is this - there are a few records which share common "Names", and therefore the graph will not display those duplicated records based upon the "Name" dimension.

Is there a way around this - to display all records in the graph, even those that have duplicated field names?

Thanks in advance!!

[sunny_talwar]

May be create a new name field for dimension in the script

LOAD DISTINCT

          Name,

          AutoNumber(Name, RowNo()) as Name_Dim

Resident.....;

Now use Name_Dim as your dimension

[msawyercke]

Hi Sunny!  Thanks for jumping in! 

Here's my script with the new section bolded:

======================================

LOAD

    Pick(Match("Owner", 'C', 'F', 'I'), 'Company - Domestic', 'Franchise - Domestic', 'International') as "Owner",

    "PS #",

    "Legacy  #",

    "Concept CD",

    Pick(Match("Bus. Unit", '010', '050', '020'), 'C', 'H', 'GB') as "Bus. Unit",

    Pick(Match("UM Status", 'O', 'P'), 'Open', 'Pending') as "UM Status",

DISTINCT

          "Unit Location",

          AutoNumber("Unit Location", RowNo()) as Name_Dim Resident,

    "Franchisee (If Applicable)",

    "Address Line 1",

    "Address Line 2",

    City,

    State,

    Postal,

    County,

    Country,

    Phone,

    Fax,

    Area,

    Region,

    "Region MGR",

    "Region MGR Phone",

    District,

    "District MGR",

    "District MGR Phone",

    "DMA Name",

    "DMA Description",

    "General Manager",

    "Distribution Center",

    Latitude,

    Longitude,

    "Current Date Time",

    "MBM ID",

    F33

FROM [lib://PS_UNIT_MASTER_QUERIES (ckrcorp_msawyer)/Daily Unit Master Query.xls]

(biff, embedded labels, header is 1 lines, table is [Daily Unit Master Query$])

Where not Match("PS #", 1506919, 1100919, 1505558, 1505559, 1505566, 1505567, 1501320);

-------------------------------------------------------------------

As a result, I get this error:

The following error occurred:

Syntax error, missing/misplaced FROM:

[sunny_talwar]

I meant more like this:

TableName:

LOAD

    Pick(Match("Owner", 'C', 'F', 'I'), 'Company - Domestic', 'Franchise - Domestic', 'International') as "Owner",

    "PS #",

    "Legacy  #",

    "Concept CD",

    Pick(Match("Bus. Unit", '010', '050', '020'), 'C', 'H', 'GB') as "Bus. Unit",

    Pick(Match("UM Status", 'O', 'P'), 'Open', 'Pending') as "UM Status",

    "Unit Location",

    "Franchisee (If Applicable)",

    "Address Line 1",

    "Address Line 2",

    City,

    State,

    Postal,

    County,

    Country,

    Phone,

    Fax,

    Area,

    Region,

    "Region MGR",

    "Region MGR Phone",

    District,

    "District MGR",

    "District MGR Phone",

    "DMA Name",

    "DMA Description",

    "General Manager",

    "Distribution Center",

    Latitude,

    Longitude,

    "Current Date Time",

    "MBM ID",

    F33

FROM [lib://PS_UNIT_MASTER_QUERIES (ckrcorp_msawyer)/Daily Unit Master Query.xls]

(biff, embedded labels, header is 1 lines, table is [Daily Unit Master Query$])

Where not Match("PS #", 1506919, 1100919, 1505558, 1505559, 1505566, 1505567, 1501320);

LinkTable:

LOAD DISTINCT "Unit Location"

    AutoNumber("Unit Location", RowNo()) as Name_Dim

Resident TableName;

[msawyercke]

Sunny,

it is being held up somewhere:

I do very much appreciate your help!

[sunny_talwar · Accepted solution]

My apologize, this might not work. Try this:

LOAD

    Pick(Match("Owner", 'C', 'F', 'I'), 'Company - Domestic', 'Franchise - Domestic', 'International') as "Owner",

    "PS #",

    "Legacy  #",

    "Concept CD",

    Pick(Match("Bus. Unit", '010', '050', '020'), 'C', 'H', 'GB') as "Bus. Unit",

    Pick(Match("UM Status", 'O', 'P'), 'Open', 'Pending') as "UM Status",

    "Unit Location",

   Dual("Unit Location", AutoNumber("Unit Location"&"Address Line 1")) as "Unit Location Dim",

    "Franchisee (If Applicable)",

    "Address Line 1",

    "Address Line 2",

    City,

    State,

    Postal,

    County,

    Country,

    Phone,

    Fax,

    Area,

    Region,

    "Region MGR",

    "Region MGR Phone",

    District,

    "District MGR",

    "District MGR Phone",

    "DMA Name",

    "DMA Description",

    "General Manager",

    "Distribution Center",

    Latitude,

    Longitude,

    "Current Date Time",

    "MBM ID",

    F33

FROM [lib://PS_UNIT_MASTER_QUERIES (ckrcorp_msawyer)/Daily Unit Master Query.xls]

(biff, embedded labels, header is 1 lines, table is [Daily Unit Master Query$])

Where not Match("PS #", 1506919, 1100919, 1505558, 1505559, 1505566, 1505567, 1501320);

I am assuming the even though the Unit Location are the same, the Address1 will be different for the two same named Unit Location. Is that a right assumption to make?

[msawyercke]

Your assumption is correct, Sunny.

I will go try your recommendation now and let you know the outcome..

MANY THANKS!

[msawyercke]

This worked, SUNNY!  See??

Thanks again - have a terrific rest of your day!

[sunny_talwar]

Awesome