Making Field Value As Appear as Total

[umartareen]

Hi All,

I have a query wherein I have two fields which need to be concatenated in a way that one field's value appears as total of its linked values from the other field.

To make it clear I have created a sample data, please view the images below.

Table :

Output Required :

In the picture "Output Required" above, I require the Country sales to be shown right below the Name wise sales in aggregation.

Please let me know how this can be achieved.

Thanks,

Umar

[Anonymous]

Umar,

I would suggest to create a composite key like this:

Can i combine two dimension together?

Merging two dimensions into one

[jagan]

Hi,

Try like this

Data:

LOAD

     ID,

Names,

Country,

Sales

FROM DataSource;

MasterData:

LOAD DISTINCT

ID,

Names AS Name_Country_Field

Resident Data;

Concatenate(MasterData)

LOAD DISTINCT

ID,

Country AS Name_Country_Field

Resident Data;

Now use Name_Country_Field as dimension in your Chart.

Hope this helps you.

Regards,

jagan.

[umartareen]

Hi Balraj,

Thank you for the response.

My requirement is not to make a composite key, as it gives an output with the two fields next to each other separated by the delimeter.

Example : (Country & '-' & Name) would produce the field output like (India-John).

My requirement is a bit different.

The field values of Country must have the respective field values of Name above it :

Example :

John	20
Josh	40
Mary	40
India	100
John	20
Josh	20
China	40

As seen above, India and China are coming as totals for the Names Field.

Please view the below pictures, as I have simplified them for a clearer understanding.

Thanks,

Umar

[umartareen]

Hi Jagan,

Thank you for the response. This is how one would work when concatenating two field values.

But the limitation is the field values do not repeat as shown in my required output.

The concern is, how to group them in a way where my Name field is aggregated by Country field.

Regards,

Umar

[jagan]

Hi,

Instead of attaching screenshot, if you attach data it would be easier to provide solution.  Check below link hope it helps you in getting the answers faster.

Qlik Community Tip: Posting Successful Discussion Threads

The best Option for this use Pivot Table with Country and Names as dimension and Sum(Sales) as expression, instead of changing your datamodel.

Regards,

Jagan.

[kavita25 · Accepted solution]

Hi,

Refer the attachment...Its similar to your requirement.

hope it helps you.

Regards,

Kavita

[settu_periasamy]

Hi,

Just Modified the kavita25 chart, Based on your Data..

It's looks like this..

[umartareen]

Great Thank you Kavita and Settu ! Works perfect.

I have also found a way to achieve this in the script level. Will post it soon.

Thanks,

Umar

[kavita25]

close the discussion..by marking as helpful or correct answer....