Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
toddbuss
Creator
Creator

inserting a "total" record into an existing dimension

I have a qlik sense application  that includes a dimension called "location" which lists 9 different locations .  from this, I build stacked bar-graphs that show the average hours at each of the locations.  I need to add a 10th location called, "all locations" so that the aggregated average can be displayed on the same  bar graph alongside the 9 other locations.    Because I'm working with averages, there will be no aesthetic issue with the "all locations" scaling up differently from the nine others.

My instinct is to arrange this during the data load, but I was wondering if it could be done with a derived dimension.   Keeping it out of the loading would maintain the integrity of the data model for things like sums.  Additionally, the loading script is already complex (to me) with crosstable loading from hundreds of excel files.  Please forgive my lack of data processing terminology.    I'm sure such a thing has been done before, but I don't know how to find it.   

Thanks,

Todd

 

sample.jpg

Labels (2)
1 Solution

Accepted Solutions
Gysbert_Wassenaar

The easiest way is to create an extra small table in the load script to create a new field that you can use instead of the current local dimension:

NewLocationWithTotal:
LOAD DISTINCT
    'Total' as LocationAndTotal, 
    Location
FROM
    ...table with location field...
    ;

CONCATENATE (NewLocationWithTotal)

LOAD DISTINCT
    Location as LocationAndTotal, 
    Location
FROM
    ...table with location field...
    ;

talk is cheap, supply exceeds demand

View solution in original post

1 Reply
Gysbert_Wassenaar

The easiest way is to create an extra small table in the load script to create a new field that you can use instead of the current local dimension:

NewLocationWithTotal:
LOAD DISTINCT
    'Total' as LocationAndTotal, 
    Location
FROM
    ...table with location field...
    ;

CONCATENATE (NewLocationWithTotal)

LOAD DISTINCT
    Location as LocationAndTotal, 
    Location
FROM
    ...table with location field...
    ;

talk is cheap, supply exceeds demand