topic Re: Script Help Needed! in App Development

Script Help Needed!

dmohanty — Mon, 07 Feb 2022 16:58:38 GMT

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 , @tresB

Re: Script Help Needed!

chrismarlow — Mon, 07 Feb 2022 18:22:38 GMT

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.

Re: Script Help Needed!

dmohanty — Mon, 07 Feb 2022 19:11:32 GMT

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.

Re: Script Help Needed!

chrismarlow — Mon, 07 Feb 2022 19:29:40 GMT

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.