Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Developers,
I have a requirement. i need to combine two same fields as a single field by doing the sum of the price for the same values. Please see the requirement below. Distinct will remove the duplicates but i dont know how to sum the 2 records price. Can anyone help me please? Thanks in advance
Account data source 1 | Account data source 2 | symbol1 | symbol 2 | Month end date1 | Month end date 2 | Price 1 | price 2 |
1234 | 1234 | abc | abc | 3/30/2016 | 3/30/2016 | 100 | 200 |
1234 | 1234 | abc | abc | 3/30/2016 | 3/30/2016 | 100 | 200 |
Final Result | |||||||
Account data source 1 | Account data source 2 | symbol1 | symbol 2 | Month end date1 | Month end date 2 | Price 1 | price 2 |
1234 | 1234 | abc | abc | 3/30/2016 | 3/30/2016 | 200 | 200 |
Hi Anil,
You can add a straight table and add all your fields as dimensions except for Price 1 and Price 2, and add the sum of both values as an expression, this will create a row like the one in your example:
But, I'm not really sure if this fulfill your requirement, maybe you could provide more details and a more extended data set to understand your need better.
Regards,
-- Karla
May be like this in script?
T1:
LOAD * INLINE [
Account data source 1, Account data source 2, symbol1, symbol 2, Month end date1, Month end date 2, Price 1, Price 2
1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200
1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200
];
NoConcatenate
T2:
LOAD [Account data source 1]
, [Account data source 2]
, [symbol1]
, [symbol 2]
, [Month end date1]
, [Month end date 2]
, Sum([Price 1]) as [Price 1]
, Sum([Price 2]) as [Price 2]
Resident T1 Group by [Account data source 1]
, [Account data source 2]
, [symbol1]
, [symbol 2]
, [Month end date1]
, [Month end date 2];
DROP Table T1;
Hi Anil,
Try like this
T1:
LOAD * INLINE [
Account data source 1, Account data source 2, symbol1, symbol 2, Month end date1, Month end date 2, Price 1, Price 2
1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200
1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200
];
NoConcatenate
T2:
load
[Account data source 1], [Account data source 2], [symbol1], [symbol 2], [Month end date1], [Month end date 2], [Price 2],
sum([Price 1]) as [Price 1]
Resident T1 Group by [Account data source 1], [Account data source 2], [symbol1], [symbol 2], [Month end date1], [Month end date 2], [Price 2];
DROP Table T1;
Note:
Try that we will get correct result what u want.