topic Re: Making Field Value As Appear as Total in QlikView

Making Field Value As Appear as Total

umartareen — Wed, 23 Dec 2015 05:58:00 GMT

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

Re: Making Field Value As Appear as Total

Anonymous — Wed, 23 Dec 2015 06:03:36 GMT

Umar,

I would suggest to create a composite key like this:

Can i combine two dimension together?

Merging two dimensions into one

Re: Making Field Value As Appear as Total

jagan — Wed, 23 Dec 2015 06:23:50 GMT

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.

Re: Making Field Value As Appear as Total

umartareen — Wed, 23 Dec 2015 06:25:38 GMT

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

Re: Making Field Value As Appear as Total

umartareen — Wed, 23 Dec 2015 06:45:28 GMT

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

Re: Making Field Value As Appear as Total

jagan — Wed, 23 Dec 2015 06:52:11 GMT

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.

Re: Making Field Value As Appear as Total

kavita25 — Wed, 23 Dec 2015 08:04:58 GMT

Hi,

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

hope it helps you.

Regards,

Kavita

Re: Making Field Value As Appear as Total

settu_periasamy — Wed, 23 Dec 2015 09:30:25 GMT

Hi,

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

It's looks like this..

Re: Making Field Value As Appear as Total

umartareen — Wed, 23 Dec 2015 10:14:07 GMT

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

Re: Making Field Value As Appear as Total

kavita25 — Wed, 23 Dec 2015 10:22:32 GMT

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