topic How to add a grouped value field to the same field in script in QlikView

How to add a grouped value field to the same field in script

benvatvandata — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

To better explain my question, I will use an example:

Table: Events

Field: ItemCode ; Values: A, B, C, D

Field: Expenses ; Values: 1, 2, 3, 4

I would like to add 'ALL' as an ItemCode and the sum of A,B,C,D expenses as it's Expenses value.

So my result would be:

Field: ItemCode ; Values: A, B, C, D, ALL

Field: Expenses ; Values: 1, 2, 3, 4, 10

What would the script look like for this?

Thanks,

Ben

Re: How to add a grouped value field to the same field in script

Gysbert_Wassenaar — Fri, 09 Dec 2016 17:10:22 GMT

Like this:

Table:

LOAD ItemCode, Expenses FROM ... ;

CONCATENATE (Table)

LOAD 'ALL' as ItemCode, sum(Expenses) as Expenses RESIDENT Table;

Re: How to add a grouped value field to the same field in script

benvatvandata — Fri, 09 Dec 2016 17:18:34 GMT

This is what I'm looking for, but I forgot to mention that there are more than just those ItemCodes, Example:

ItemCode: A, B, C, D, E, F

Values: 1, 2, 3, 4, 5, 6

I want 'ALL' to just consist of A, B, C, D; So I want:

ItemCode: A, B, C, D, ALL, E, F

Values: 1, 2, 3, 4, 10, 5, 6

Re: How to add a grouped value field to the same field in script

Gysbert_Wassenaar — Fri, 09 Dec 2016 17:22:04 GMT

Change the second load to:

LOAD 'ALL' as ItemCode, sum(Expenses) as Expenses RESIDENT Table WHERE Match(ItemCode, 'A','B','C','D');

Re: How to add a grouped value field to the same field in script

benvatvandata — Fri, 09 Dec 2016 17:53:54 GMT

This works for my example, but my example may have been too simple - let me go into further detail:

I have other fields that this field is being filtered on, for example my set analysis is:

sum({<

MTGStatus = {'ASSIGNED','CLOSED','CLOSED BY FINANCE',

'LOGISTICS CONFIRMED','PENDING/REP','PENDING/SPEAKER','PENDING/VENUE',

'PROGRAM OCCURRED','RECONCILED', 'REQUESTED BY REP'},

MTGDate = {">=$(=yearstart(today()))"} >} Expenses)

And the first Dimension is Division - my 2nd Dimension is ItemCode

These fields are all in the same table, can this be done?

Re: How to add a grouped value field to the same field in script

MarcoWedel — Fri, 09 Dec 2016 23:00:51 GMT

Hi,

why are you looking for a script solution?

Maybe you just could use a total label to get a table like this (eventually restricting the sum function with your existing set expression)?

hope this helps

regards

Marco

Re: How to add a grouped value field to the same field in script

benvatvandata — Fri, 09 Dec 2016 23:20:36 GMT

I have bar chart with grouped style ('stacked' would do exactly what I want, but it is desired to be grouped style), in Qlik Sense, and I'm trying to display a total sum for each group in addition to showing the other bars grouped with it. In QlikSense it will only allow one expression with 2 dimensions.

Re: How to add a grouped value field to the same field in script

Gysbert_Wassenaar — Sat, 10 Dec 2016 12:39:54 GMT

Perhaps you should create a new table with a new ReportItemCode field and use that field as chart dimension

NewTable:

LOAD

FieldValue('ItemCode', RecNo()) as ReportItemCode,

FieldValue('ItemCode', RecNo()) as ItemCode

AutoGenerate FieldValueCount('ItemCode')

;

CONCATENATE (NewTable)

LOAD * INLINE [

ReportItemCode, ItemCode

ALL, A

ALL, B

ALL, C

ALL, D

];

Re: How to add a grouped value field to the same field in script

benvatvandata — Sat, 10 Dec 2016 18:29:09 GMT

For some reason I'm getting an error with your script?

Re: How to add a grouped value field to the same field in script

Gysbert_Wassenaar — Sat, 10 Dec 2016 19:02:58 GMT

And you want me to guess the error message? No thanks, not interested

Ok, one guess. You replaced your entire script with the bit I posted instead of appending it to your existing script. The part I posted does need the table with the regular ItemCode field to exist already.

Re: How to add a grouped value field to the same field in script

benvatvandata — Sat, 10 Dec 2016 20:06:05 GMT

take it easy... of course I appended it, I missed a quotation mark ha - but no expenses are shown?

Re: How to add a grouped value field to the same field in script

Gysbert_Wassenaar — Sun, 11 Dec 2016 12:46:02 GMT

What field(s) are you using as dimension in your chart and what are the expressions you use?

Re: How to add a grouped value field to the same field in script

benvatvandata — Mon, 12 Dec 2016 02:53:02 GMT

Dim1: Division

Dim2: ReportItemCode

Exp:

sum({<

MTGStatus = {'ASSIGNED','CLOSED','CLOSED BY FINANCE',

'LOGISTICS CONFIRMED','PENDING/REP','PENDING/SPEAKER','PENDING/VENUE',

'PROGRAM OCCURRED','RECONCILED', 'REQUESTED BY REP'},

MTGDate = {">=$(=yearstart(today()))"} >} Expenses)

Thanks,

Ben

Re: How to add a grouped value field to the same field in script

Anil_Babu_Samineni — Mon, 12 Dec 2016 03:21:35 GMT

Your question is related to Script level. Have you seen Gysbert first reply

This in bar chart?

Re: How to add a grouped value field to the same field in script

Gysbert_Wassenaar — Mon, 12 Dec 2016 09:38:58 GMT

So, that same expression does return results if you use ItemCode as dimension, but not if you use ReportItemCode?

Re: How to add a grouped value field to the same field in script

benvatvandata — Fri, 16 Dec 2016 19:00:01 GMT

Phew, sorry it took me so long to respond - creating a new field eventually worked with my model. Thanks gysbert!

-Ben