Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
UserID2626
Creator III
Creator III

Adding row manually to the loaded table in qlik sense Script

Hi,

I want add a row manually in Qlik Script.

Source Table:

Product_ID Product_name Quantity Amount Total(Quantity*Amount)
1234_AB ABCD 3 15 45
1234_BC ASDF 2 50 100
1234_AB qwer 1 20 20
1234_xy zxcv 5 12 60

 

Result:

Product_ID Product_name Quantity Amount Total(Quantity*Amount)
1234_AB ABCD 3 15 45
1234_BC ASDF 2 50 100
1234_AB qwer 4 20 80
1234_xy zxcv 5 12 60
Total Exclude ASDF 12 47 185
Avg Exclude ASDF      

 

Kindly help to add total and Avg column manually in script without using inline table method.

 

Regards...

Labels (4)
1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II

Try this:

//Adding Total Row
Concatenate(SourceTable)
Load
	'Total' 		as Product_ID,
	'Exclude ASDF'	as Product_name,
	sum(Quantity)	as Quantity,
	sum(Amount) 	as Amount,
	sum([Total(Quantity*Amount)]) as [Total(Quantity*Amount)]
Resident SourceTable
	where not match(Product_name,'ASDF')
		group by 'Total','Exclude ASDF';

//Adding AVG Row
Concatenate(SourceTable)
Load
	'Avg' 		as Product_ID,
	'Exclude ASDF'	as Product_name,
	avg(Quantity)	as Quantity,
	avg(Amount) 	as Amount,
	avg([Total(Quantity*Amount)]) as [Total(Quantity*Amount)]
Resident SourceTable
	where not match(Product_name,'ASDF')
		group by 'Avg','Exclude ASDF';

 

The result seems correct:

2019-06-24 08_24_03-QlikView x64 - Copia del rivenditore - [M__test.qvw_].png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

1 Reply
micheledenardi
Specialist II
Specialist II

Try this:

//Adding Total Row
Concatenate(SourceTable)
Load
	'Total' 		as Product_ID,
	'Exclude ASDF'	as Product_name,
	sum(Quantity)	as Quantity,
	sum(Amount) 	as Amount,
	sum([Total(Quantity*Amount)]) as [Total(Quantity*Amount)]
Resident SourceTable
	where not match(Product_name,'ASDF')
		group by 'Total','Exclude ASDF';

//Adding AVG Row
Concatenate(SourceTable)
Load
	'Avg' 		as Product_ID,
	'Exclude ASDF'	as Product_name,
	avg(Quantity)	as Quantity,
	avg(Amount) 	as Amount,
	avg([Total(Quantity*Amount)]) as [Total(Quantity*Amount)]
Resident SourceTable
	where not match(Product_name,'ASDF')
		group by 'Avg','Exclude ASDF';

 

The result seems correct:

2019-06-24 08_24_03-QlikView x64 - Copia del rivenditore - [M__test.qvw_].png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.