Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
msawyercke
Not applicable

Bar Graph Question - Duplicated Dimenison Fields

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

Graph.JPG

And here is the Dimension screenshot:

Graph Dimension.JPG

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

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

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?

8 Replies
sunny_talwar
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

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
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

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
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

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
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

Sunny,

it is being held up somewhere:

Error Capture.JPG

I do very much appreciate your help!

sunny_talwar
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

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
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

Your assumption is correct, Sunny.

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

MANY THANKS!

msawyercke
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

This worked, SUNNY!  See??

Solved.JPG

Thanks again - have a terrific rest of your day!

sunny_talwar
Not applicable

Re: Bar Graph Question - Duplicated Dimenison Fields

Awesome