Giving value to duplications based on ruling

[Shir63]

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 🙂

[Taoufiq_Zarra]

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 :

[Shir63]

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 🙂

[Taoufiq_Zarra]

normally yes
this is what I get for

115 A 100

115 C 100

unless I misunderstood the request.

[Shir63]

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?

[Shir63]

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

[Taoufiq_Zarra]

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;

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output.

[Saravanan_Desingh · Accepted solution]

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;