Calucate sum with specific condition

[jacek_k]

Sample data:

LOAD * INLINE [
date, index, value
1.01.2022, a, 100
1.01.2022, b, 50
2.02.2022, b, 100
];

I want to calculate sum(value) but
if index = a and index = b per exist same day sum only index = a (a have more priority, ignore b)
But if b only exist per same day sum(value) if index = b if only index a exist sum(value)

I need script flag to use it in set analysis. 

[sidhiq91]

@jacek_k  could you please elaborate a little bit more clearly, i am sorry i am not able to understand.

[jacek_k]

I want create flag 1 or 0. Example below:

Date, ID, Flag
1.01.2020, A, 0

1.01.2020 B, 1

If the same date and ID = A and B put 1 in B row, put 0 in A row. 

[MayilVahanan]

Hi

Are you looking like this?

LOAD *, If(date = Peek(date), 0, 1) as flag INLINE [
date, index, value
1.01.2022, a, 100
1.01.2022, b, 50
2.02.2022, b, 100
];

[jacek_k]

test:
LOAD * INLINE [
date, index, value
1.01.2022, a, 100
1.01.2022, b, 50
2.01.2022, b, 100
2.01.2022,a,20
1.01.2022,b,30
3.01.2022,a,100
4.01.2022,b,75

];

Load *,
if(date = peek(date),0,1) as flag
Resident test;
drop table test;

So, I add more dates and the results are incorrect. 
Please look on 1.01.2022 only index a = 1, b = must be 0
Also 2.01.2022 only a = 1, b must be 0.

So if a and b exist per same day put 1 to a, 0 to b.
if exist only one date b put 1
if exist only one date a put 1

[MayilVahanan]

HI

If the date is not in order, then try like below

test:
LOAD * INLINE [
date, index, value
1.01.2022, a, 100
1.01.2022, b, 50
2.01.2022, b, 100
2.01.2022,a,20
1.01.2022,b,30
3.01.2022,a,100
4.01.2022,b,75
];

Final:

LOAD *, If(date = Peek(date), 0, 1) as flag Resident test order by date;

DROP Table test;

[jacek_k]

Hi, please look on 2.01.2022 because index a has more priority flag must by 1.

1.01.2022 a and b =put 1 on a - correct
3.01.2022 a only - 1 flag correct
4.01.2022 b only - 1 flag correct

2.01.2022 a and b exist - put flag only a

[MayilVahanan]

Hi

if the order is different, you can add order by with both fields

LOAD *, If(date = Peek(date), 0, 1) as flag Resident test order by date, index;

[jacek_k]

I add more records:

test:
LOAD * INLINE [
date, index, value
1.01.2022, a, 100
1.01.2022, b, 50
2.01.2022, b, 100
1.01.2022, a, 30
2.01.2022,a,20
1.01.2022,b,30
3.01.2022,a,100
4.01.2022,b,75
];

Final:

LOAD *, If(date = Peek(date), 0, 1) as flag Resident test order by date,index;

DROP Table test;

As you can see above all 1.01.2022 index = a flag must be 1.

[MayilVahanan]

HI

Try like below

test:
LOAD * INLINE [
date, index, value
1.01.2022, a, 100
1.01.2022, b, 50
2.01.2022, b, 100
1.01.2022, a, 30
2.01.2022,a,20
1.01.2022,b,30
3.01.2022,a,100
4.01.2022,b,75
];

join
LOAD date, FirstSortedValue(distinct index, -value) as index, 1 as flag Resident test Group by date;