Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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:
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: