8 Replies Latest reply: Sep 21, 2016 3:01 PM by Sunny Talwar

Bar Graph Question - Duplicated Dimenison Fields

Mike Sawyer Sep 21, 2016 10:25 AM

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!!

Re: Bar Graph Question - Duplicated Dimenison Fields

Sunny Talwar Sep 21, 2016 12:56 PM (in response to Mike Sawyer)

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
Like Show 0 Likes (0)

Actions
- Re: Bar Graph Question - Duplicated Dimenison Fields
  
  Mike Sawyer Sep 21, 2016 1:47 PM (in response to Sunny Talwar )
  
  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:
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Bar Graph Question - Duplicated Dimenison Fields
    
    Sunny Talwar Sep 21, 2016 2:07 PM (in response to Mike Sawyer)
    
    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;
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Bar Graph Question - Duplicated Dimenison Fields
      
      Mike Sawyer Sep 21, 2016 2:17 PM (in response to Sunny Talwar )
      
      Sunny,
      
      it is being held up somewhere:
      
      I do very much appreciate your help!
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Bar Graph Question - Duplicated Dimenison Fields
        
        Sunny Talwar Sep 21, 2016 2:41 PM (in response to Mike Sawyer)
        
        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?
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: Bar Graph Question - Duplicated Dimenison Fields
        
        Mike Sawyer Sep 21, 2016 2:43 PM (in response to Sunny Talwar )
        
        Your assumption is correct, Sunny.
        
        I will go try your recommendation now and let you know the outcome..
        
        MANY THANKS!
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Bar Graph Question - Duplicated Dimenison Fields
        
        Mike Sawyer Sep 21, 2016 2:56 PM (in response to Sunny Talwar )
        
        This worked, SUNNY! See??
        
        Thanks again - have a terrific rest of your day!
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Bar Graph Question - Duplicated Dimenison Fields
        
        Sunny Talwar Sep 21, 2016 3:01 PM (in response to Mike Sawyer)
        
        Awesome
        
        Like Show 1 Like (1)
        
        Actions

Go to original post