Script Help Needed!

[dmohanty]

Hi Qlik Team,

Need a scripting help for this problem:

I have a raw data set like this below first table, where 2nd Row and 3rd Row 3rd are kind of duplicates.

• $50 from 2nd Row is already added to 1st Row and shown as $1550.
• $70 from 3rd Row is already added to 4th Row and shown as $1070.

ACCOUNT	LINE_NUM	ITEM_NUM	DOLLAR
ABC	10	101	$1,550
ABC	10	303	$50
ABC	20	303	$70
ABC	20	202	$1,070

Requirement:

• First Need to create a FLAG field with 2 values: 'Exclude', 'Include'.
• When Exclude is selected, the output of DOLLAR Column should be : 

ACCOUNT	LINE_NUM	ITEM_NUM	DOLLAR
ABC	10	101	$1,500 (calculated as 1550 - 50, where LINE_NUM = LINE_NUM)
ABC	20	202	$1,000 (calculated as 1070 - 70, where LINE_NUM = LINE_NUM)

• When Include is selected, the output of DOLLAR Column should be : 

ACCOUNT	LINE_NUM	ITEM_NUM	DOLLAR
ABC	10	101	$1,550
ABC	20	202	$1,070

Can someone please help in framing the Load statement OR Chart Expression for this? 

@Gysbert_Wassenaar , @rwunderlich , @sunny_talwar ,  @marcus_sommer , @tresesco 

[chrismarlow]

Hi,

Maybe, script;

data:
Load * inline [
ACCOUNT,	LINE_NUM,	ITEM_NUM,	DOLLAR
ABC,	10,	101,	1550
ABC,	10,	303,	50
ABC,	20,	303,	70
ABC,	20,	202,	1070
];

flag:
Load * inline [
FLAG
Exclude
Include
];

Plus expression as shown;

Cheers,

Chris.

[dmohanty]

Hi @chrismarlow ,

Thanks much for responding on this. Quite a nice approach. 

But to tell you more - the values are just sample and I can't really hardcode '303' in Expression. There are thousands of such entries in actual database. 

Also - I was looking if we can compare the LINE_NUM = LINE NUM in anyways and do a Subtraction, that might solve it. 

[chrismarlow]

Hi,

So you are really going to have to nail what the rule is to decide what gets subtracted from what ... if there are 'always' 2 positive numbers and you subtract the smallest from the largest, then could adapt;

But just like 303 will fall over if there are different patterns.

Cheers,

Chris.