Remove the column for which same Product Number Comes 3 times

[rishikeshtiwari]

Hi Guys,

Good evening

I am facing an issue.

Scenerio is that I have a Product table . There are four fields in this table

Prefix

ProductNumber

ProductName

ProductDesc

Prefix is a field having two values 10 & 20

Prooduct Number is a field which having ProductNumber  and 10-ProductNumber  (Prefix & ProductNumber) .

Please see this excel in result tab

ProductNumber is comming alone in column2  not with prefix.

10                        1001               ABCD

ProductNumber is comming with Prefix 10 in Column3

10                      10-1001                ABCD

Again the same Product Number is comming with Prefix 20 (Column 4)

So I want one Product Number should not come three times,if present then that column should

remove for prefix 20 row only not Prefix 10 row .(Yellow marked column should only remove from table).

Same for column 7 and 12 (Yellow marked).

But if any ProductNumber not comes three times then Prefix 20 column will be required.

not need to delete.

Please handle this logic in script (Back-end).

The final result should FinalTable tab.

Kindly suggest me the solution

[maxgro]

RESULT

SCRIPT

Directory;

source:

LOAD

  Prefix,

    ProductNumber,

    ProductName,

    ProductDesc,

    if(index(ProductNumber,'-'), subfield(ProductNumber, '-',2), ProductNumber) as BasePN

FROM

DataSource.xls

(biff, embedded labels, table is Product$);

Left Join (source)

load BasePN, count(BasePN) as BasePNCount Resident source group by BasePN;

final:

load

  Prefix,

    ProductNumber,

    ProductName,

    ProductDesc

    //,BasePN

Resident source   

where BasePNCount ❤️ or (BasePNCount=3 and Prefix <> 20);

DROP Table source;

[Clever_Anjos]

Line 16 should be returned?