topic Re: Best Approach to grouping values into a new column in QlikView

Best Approach to grouping values into a new column

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

I have a file which contains multiple rows whereby I am adding 2 columns together (InvoiceNetAmount + InvoiceTaxAmount) to create a new one (InvoiceAmount).

Customer Number	Invoice Number	Invoice Line ID	Invoice Net Amount	Invoice Tax Amount	Invoice Amount
1	1	1	3.00	2.00	5.00
1	1	2	3.00	2.00	5.00
2	2	1	3.00	2.00	5.00

I wish then to sum the columns related to a certain grouping i.e. CustomerNumber and InvoiceNumber to create a new column on the next file I load, the above values would show the following

Customer Number	Invoice Number	Invoice Amount
1	1	10.00
1	2	5.00

Is the best approach to create a temporary table which i then join the results of this to the next table I load. Or am I over complicating things?

Any help appreciated - have attached a sample qvw

Re: Best Approach to grouping values into a new column

sunny_talwar — Wed, 21 Sep 2016 13:58:32 GMT

In the script or front end?

Re: Best Approach to grouping values into a new column

martin_hamilton — Wed, 21 Sep 2016 14:03:00 GMT

Hi Sunny

In the script - I thought it would be easier doing it that way.

Thanks

Martin

Re: Best Approach to grouping values into a new column

martin_hamilton — Wed, 21 Sep 2016 14:03:27 GMT

Saying that I am not sure of best practice in this circumstance.

thanks

Martin

Re: Best Approach to grouping values into a new column

agomes1971 — Wed, 21 Sep 2016 14:07:56 GMT

Hi,

please see attached.

HTH

André Gomes

Re: Best Approach to grouping values into a new column

martin_hamilton — Wed, 21 Sep 2016 14:12:28 GMT

Hi Andre, Doesnt seem to be any attachment.

Thanks

Martin

Re: Best Approach to grouping values into a new column

agomes1971 — Wed, 21 Sep 2016 14:33:35 GMT

Re: Best Approach to grouping values into a new column

Open the link.

Regards

André Gomes

Re: Best Approach to grouping values into a new column

martin_hamilton — Wed, 21 Sep 2016 14:55:03 GMT

Hi Andre

Thats works in the dashboard but I was looking to have it included in the script - is that possible?

thanks

Martin

Re: Best Approach to grouping values into a new column

sunny_talwar — Wed, 21 Sep 2016 15:01:21 GMT

May be this:

Table:

LOAD [Customer Number],

[Invoice Number],

[Invoice Line ID],

[Invoice Net Amount],

[Invoice Tax Amount]

FROM

[https://community.qlik.com/thread/233626]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD [Customer Number],

[Invoice Number],

Sum(RangeSum([Invoice Net Amount], [Invoice Tax Amount])) as [Invoice Amount]

Resident Table

Group By [Customer Number], [Invoice Number];

Re: Best Approach to grouping values into a new column

el_aprendiz111 — Wed, 21 Sep 2016 15:48:11 GMT

Hi,