topic Re: Giving value to duplications based on ruling in App Development

Giving value to duplications based on ruling

Shir63 — Sat, 16 Nov 2024 18:24:33 GMT

Hi Guys,

hope you can help me with this one:

I have duplications in my data (and I need it). I want to build an "Adjusted Price" column:

1. if line id is duplicated then Adjusted Price would be 0, except the "B" one, that should get the original price.

2. if line id is not duplicated then I want the price to be as the original Price.

3. for lines where B does not exist and there is duplication, then randomly give original price to one and the other ones would get 0.

ultimately it should like this:

Order Id	Modifier	Original Price	Adjusted Price
111	A	100	0
111	B	100	100
111	C	100	0
111	D	100	0
112	B	200	200
112	C	200	0
113	D	170	170
113	C	170	0
113	A	170	0
114	A	125	125
115	C	110	110
115	D	110	0

I have built a column to recognize the duplicated id using:

left join Table:

load Distinct " Order Id",
If(Count( "Order Id")>1,1) as Duplicate

resident Discount
Group By "Order Id";

and also I have built a formula for this to work, but it doesn't work for all cases:

IF(Original Price- PEEK(Original Price)=0 and duplicate = '1' and Peek("Order Id") = "Order Id" and MODIFIER <> 'B',0,
if (duplicate = '1' and MODIFIER <> 'B', Original Price)) AS "Price Adjusted"

please help me with your wisdom 🙂

Re: Giving value to duplications based on ruling

Taoufiq_Zarra — Mon, 06 Jul 2020 13:10:06 GMT

Hi,

Maye be this :

Data: LOAD * INLINE [ Order Id,Modifier, Original Price 111, A, 100 111, B, 100 111, C, 100 111, D, 100 112, B, 200 112, C, 200 113, D, 170 113, C, 170 113, A, 170 114, A, 125 ]; output: noconcatenate load *,if(rowno()=1 or ([Order Id]=peek([Order Id]) and Modifier<>'B' ) ,0,[Original Price]) as [Adjusted Price] resident Data order by [Order Id]; drop table Data;

output :

Re: Giving value to duplications based on ruling

Shir63 — Mon, 06 Jul 2020 13:36:56 GMT

thank you for your answer.

but your solution does not fix my issue where there are duplication and no B

like:

Order id modifier original adjusted

115 A 100 100

115 C 100 0

appreciate your help 🙂

Re: Giving value to duplications based on ruling

Taoufiq_Zarra — Mon, 06 Jul 2020 13:46:10 GMT

normally yes
this is what I get for

115 A 100

115 C 100

unless I misunderstood the request.

Re: Giving value to duplications based on ruling

Shir63 — Mon, 06 Jul 2020 15:01:53 GMT

you are right, and your solution works for that data base 100%

but for my larger data base there are cases where the ("Order Id"=peek("Order Id")) does not work as expected.

i hope i'm explaining this right:

i created a formula to help: if("Order Id"=peek("Order Id"),1,0) as Duplicate

and i see cases where :

Order ID Modifier Original Price adjusted price duplicate

111 A 100 100 0

111 B 100 100 1

so in cases where the "peek" formula returns 1 to the B line, the adjusted price would stay the same.

in cases where the peek returns 1 to A, that works great.

Order ID Modifier Original Price adjusted price duplicate

111 B 100 100 0

111 A 100 0 1

can we use something other than "Peek" or to adjust it?

Re: Giving value to duplications based on ruling

Shir63 — Mon, 06 Jul 2020 15:11:58 GMT

hi , thanks again. and yes you are correct for this database it is working 100%.

yet at my database there are cases where this doesn't work because of the "Peek" formula,

let me explain:

I have created another formula to help:

if("Order Id"=peek("Order Id"),1,0) as Duplicate,

so if the Peek formula receives 1 for B, then it would return the original price, instead of 0

Order Id Modifier Original Price Adjusted Price Duplicate

111 A 100 100 0

111 B 100 100 1

the other option is where the peek formula gives 1 to the A

Order Id Modifier Original Price Adjusted Price Duplicate

111 B 100 100 0

111 A 100 0 1

this must have something with the sorting? or we can use the different formula other than "Peek"?

thanks again

Shir

Re: Giving value to duplications based on ruling

Taoufiq_Zarra — Mon, 06 Jul 2020 15:19:19 GMT

What if you use previsous? or can you share a sample with this particular case and the output so that I can understand better

Data: LOAD * INLINE [ Order Id,Modifier, Original Price 111, A, 100 111, B, 100 111, C, 100 111, D, 100 112, B, 200 112, C, 200 113, D, 170 113, C, 170 113, A, 170 114, A, 125 ]; output: noconcatenate load *,if(rowno()=1 or ([Order Id]=previous([Order Id]) and Modifier<>'B' ) ,0,[Original Price]) as [Adjusted Price] resident Data order by [Order Id]; drop table Data;

Re: Giving value to duplications based on ruling

Saravanan_Desingh — Mon, 06 Jul 2020 19:27:28 GMT

One solution is.

tab1: LOAD *, AutoNumber(RowNo(),[Order Id]) As Key INLINE [ Order Id,Modifier, Original Price 111, A, 100 111, B, 100 111, C, 100 111, D, 100 112, B, 200 112, C, 200 113, D, 170 113, C, 170 113, A, 170 114, A, 125 115, A, 110 115, C, 110 ]; Left Join(tab1) LOAD [Order Id], If(Index(Concat(DISTINCT Modifier),'B'),'Y','N') As B_Exist Resident tab1 Group By [Order Id]; tab2: LOAD *, If((B_Exist='Y' And Modifier='B') Or (B_Exist='N' And Key=1),[Original Price],0) As [Adjusted Price] Resident tab1; Drop Table tab1;

Re: Giving value to duplications based on ruling

Saravanan_Desingh — Mon, 06 Jul 2020 19:28:15 GMT

Output.

Re: Giving value to duplications based on ruling

Saravanan_Desingh — Tue, 07 Jul 2020 02:36:34 GMT

I just forgot the 'random' part. Please check this.

tab1: LOAD *, AutoNumber(RowNo(),[Order Id]) As Key INLINE [ Order Id,Modifier, Original Price 111, A, 100 111, B, 100 111, C, 100 111, D, 100 112, B, 200 112, C, 200 113, D, 170 113, C, 170 113, A, 170 114, A, 125 115, A, 110 115, C, 110 ]; Left Join(tab1) LOAD [Order Id], If(Index(Concat(DISTINCT Modifier),'B'),'Y','N') As B_Exist, Ceil(Rand()*Max(Key)) As MKey Resident tab1 Group By [Order Id]; tab2: LOAD *, If((B_Exist='Y' And Modifier='B') Or (B_Exist='N' And Key=MKey),[Original Price],0) As [Adjusted Price] Resident tab1; Drop Table tab1;

Re: Giving value to duplications based on ruling

Shir63 — Tue, 07 Jul 2020 12:39:50 GMT

Thank you!

that worked.

for larger data samples i would not suggest to use " Autonumber" as it slows the model, or makes it impossible,

i have used a different approach for the autonumber:

IF("Sale Order Line Id" = Previous("Sale Order Line Id"), RangeSum(Peek(ID),1),1) as Key

thank you all for the help!!

you guys are the best

Re: Giving value to duplications based on ruling

Saravanan_Desingh — Tue, 07 Jul 2020 13:21:22 GMT

you welcome