Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alina_qlik
Contributor III
Contributor III

Expression to ignore rows in calculation based on two different dimension value

Hello Experts ,

What can be put as expression for sum of Net earn wherein i can ignore such rows where Value of L5 is blank and L4 has multiple entries of similar record ?

L1L2L3L4L5Net_Earn
TradeTrade DTrade d L1CONSUMABLES 2,156
TradeTrade DTrade d L1EFD 13,368
TradeTrade DTrade d L1EFDEDFO8,092
TradeTrade DTrade d L1EFDLO5,253
TradeTrade DTrade d L2ESDIO6,860
TradeTrade DTrade d L2ESDSO6,671
TradeTrade DTrade d L2FDOSSE6,178
TradeTrade DTrade d L2FPL 3,882
TradeTrade DTrade d L2SIP 4,002
TradeTrade DTrade d L2SIPIPO2,001
TradeTrade DTrade d L2SIPLLC2,001

 

Thank You all !! 

1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

Something like this?

 

Test:
NOCONCATENATE
LOAD 
	RowNo() AS ID, 
    L1 & '|' & L2 & '|' & L3 & '|' & L4 AS CheckDup,
	*
;
LOAD * INLINE [
      L1,	 L2,	  L3,	L4,	  L5, 				 Net_Earn
      Trade, Trade D, Trade d L1, CONSUMABLES, ,     2156
      Trade, Trade D, Trade d L1, EFD,	 	   ,	 13368
      Trade, Trade D, Trade d L1, EFD,		   EDFO, 8092
      Trade, Trade D, Trade d L1, EFD,		   LO,	 5253
      Trade, Trade D, Trade d L2, ESD,		   IO,	 6860
      Trade, Trade D, Trade d L2, ESD,		   SO,	 6671
      Trade, Trade D, Trade d L2, FDO,		   SSE,	 6178
      Trade, Trade D, Trade d L2, FPL,	 	   ,	 3882
      Trade, Trade D, Trade d L2, SIP,	 	   ,	 4002
      Trade, Trade D, Trade d L2, SIP,		   IPO,	 2001
      Trade, Trade D, Trade d L2, SIP,		   LLC,	 2001
];

 

 

Sum({<[ID] -= {"=Aggr(Count({1} [CheckDup]), [CheckDup]) > 1 And Len(Trim([L5])) = 0"}>} [Net_Earn])

 

View solution in original post

8 Replies
JGMDataAnalysis
Creator III
Creator III


I think I have not understood your requirement very well but this is my possible resolution:

Script:

clipboard_image_0.png

Chart Expression:

Sum({<[ID] -= {"=Aggr(Count({1} [L4]), [L4]) > 1 And Len(Trim([L5])) = 0"}>} [Net_Earn])

 

 

alina_qlik
Contributor III
Contributor III
Author

Thanks for your reply , but the expression does not seem to work .. 


sunny_talwar

So, what you have shared is the input data? What would you want the output to look like? Both these rows to now show up?

image.png

JGMDataAnalysis
Creator III
Creator III

I interpreted that you needed to exclude the two records that I highlight in the screenshot since they are the only ones that simultaneously meet the two conditions you mention.

"What can be put as expression for sum of Net earn wherein i can ignore such rows where Value of L5 is blank and L4 has multiple entries of similar record?"

clipboard_image_1.png

 

alina_qlik
Contributor III
Contributor III
Author

Thank you for reaching out to my doubt .

I require something like this 

L1L2L3L4L5Net_Earn
TradeTrade DTrade d L1CONSUMABLES 2,156
TradeTrade DTrade d L1EFD 13,368
TradeTrade DTrade d L1EFDEDFO8,092
TradeTrade DTrade d L1EFDLO5,253
TradeTrade DTrade d L2ESDIO6,860
TradeTrade DTrade d L2ESDSO6,671
TradeTrade DTrade d L2FDOSSE6,178
TradeTrade DTrade d L2FPL 3,882
TradeTrade DTrade d L2SIP 4002
TradeTrade DTrade d L2SIPIPO2,001
TradeTrade DTrade d L2SIPLLC2,001

 

OLD Total 60,464
NEW Total 43,094

 

Any L4 record which do not have L5 data and has multiple entries should be omitted . 

Ex . one row of EFD does not haev any L5 and also there are multiple entries of L4  , with similar values in L1,L2,L3,L4 

Same goes for SIP as well . 

alina_qlik
Contributor III
Contributor III
Author

********** multiple entries of EFD 

JGMDataAnalysis
Creator III
Creator III

Something like this?

 

Test:
NOCONCATENATE
LOAD 
	RowNo() AS ID, 
    L1 & '|' & L2 & '|' & L3 & '|' & L4 AS CheckDup,
	*
;
LOAD * INLINE [
      L1,	 L2,	  L3,	L4,	  L5, 				 Net_Earn
      Trade, Trade D, Trade d L1, CONSUMABLES, ,     2156
      Trade, Trade D, Trade d L1, EFD,	 	   ,	 13368
      Trade, Trade D, Trade d L1, EFD,		   EDFO, 8092
      Trade, Trade D, Trade d L1, EFD,		   LO,	 5253
      Trade, Trade D, Trade d L2, ESD,		   IO,	 6860
      Trade, Trade D, Trade d L2, ESD,		   SO,	 6671
      Trade, Trade D, Trade d L2, FDO,		   SSE,	 6178
      Trade, Trade D, Trade d L2, FPL,	 	   ,	 3882
      Trade, Trade D, Trade d L2, SIP,	 	   ,	 4002
      Trade, Trade D, Trade d L2, SIP,		   IPO,	 2001
      Trade, Trade D, Trade d L2, SIP,		   LLC,	 2001
];

 

 

Sum({<[ID] -= {"=Aggr(Count({1} [CheckDup]), [CheckDup]) > 1 And Len(Trim([L5])) = 0"}>} [Net_Earn])

 
JGMDataAnalysis
Creator III
Creator III

Another alternative defining flags in script and avoiding the Aggr function.

SET HidePrefix = '_';

Test:
NOCONCATENATE
LOAD
	RowNo() AS ID,
    If(Len(Trim(L5)), 0, 1) AS _FlagEmptyL5,
    L1 & '|' & L2 & '|' & L3 & '|' & L4 AS CheckDupTemp,
	*
;
LOAD * INLINE [
      L1,	 L2,	  L3,	L4,	  L5, 				 Net_Earn
      Trade, Trade D, Trade d L1, CONSUMABLES, ,     2156
      Trade, Trade D, Trade d L1, EFD,	 	   ,	 13368
      Trade, Trade D, Trade d L1, EFD,		   EDFO, 8092
      Trade, Trade D, Trade d L1, EFD,		   LO,	 5253
      Trade, Trade D, Trade d L2, ESD,		   IO,	 6860
      Trade, Trade D, Trade d L2, ESD,		   SO,	 6671
      Trade, Trade D, Trade d L2, FDO,		   SSE,	 6178
      Trade, Trade D, Trade d L2, FPL,	 	   ,	 3882
      Trade, Trade D, Trade d L2, SIP,	 	   ,	 4002
      Trade, Trade D, Trade d L2, SIP,		   IPO,	 2001
      Trade, Trade D, Trade d L2, SIP,		   LLC,	 2001
];

LEFT JOIN (Test)
LOAD CheckDupTemp, If(Count(CheckDupTemp) > 1, 1, 0) AS _FlagDup 
RESIDENT Test GROUP BY CheckDupTemp;

DROP FIELD CheckDupTemp;

 

Sum({<[ID] -= {"=[_FlagEmptyL5] And [_FlagDup]"}>} [Net_Earn])