If condition in script

[MEBG93]

Hello experts,

I'm having trouble in getting this around. So I've loaded a table from several sources that need to follow these conditions in script:

1. I've created a flag column, either 1 or 0. If Receipt Number (column) counts more than 1, then only show the row containing 1, else show 0.
2. The resulting table has to be with distinct values in Receipt Number, always.

I've tried using max() in flag column, but it only works partially. There are still non-distinct Receipt Numbers. Also tried an if condition where if(count("Receipt Number")>1, flag=1, flag) but doesn't work.

Any suggestions?

Thanks!

[Vegar]

I don't completely understand your problem reading your description. Could you create a smal mockup dataset and a script to explain your issue?

[MEBG93]

I have this table:

table1:

load inline * [

Receipt, Status, Amount, Days, flag

A1234, OK, 1000, 10, 1

A1234, Not OK, 1000, 5, 0

A4321, OK, 1000, 10, 1

A4321, Not OK, 1000, 5, 0

B1234, OK, 1000, 10, 0

];

I need to keep rows where Receipt is distinct, and max(flag)

table:

load inline * [

Receipt, Status, Amount, Days, flag

A1234, OK, 1000, 10, 1

A4321, OK, 1000, 10, 1

B1234, OK, 1000, 10, 0

];

[Vegar · Accepted solution]

Try this:

table1:
load * inline [
Receipt, Status, Amount, Days, flag
A1234, OK, 1000, 10, 1
A1234, Not OK, 1000, 5, 0
A4321, OK, 1000, 10, 1
A4321, Not OK, 1000, 5, 0
B1234, OK, 1000, 10, 0
];
Right join (table1)
LOAD Receipt, max(flag) as flag
Resident table1
group by Receipt
;

[MEBG93]

Thanks!