topic Re: Remove the column for which same Product Number Comes 3 times in QlikView

Remove the column for which same Product Number Comes 3 times

rishikeshtiwari — Mon, 01 Jun 2015 17:54:24 GMT

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

Re: Remove the column for which same Product Number Comes 3 times

maxgro — Mon, 01 Jun 2015 18:16:31 GMT

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;

Re: Remove the column for which same Product Number Comes 3 times

Clever_Anjos — Mon, 01 Jun 2015 18:20:43 GMT

Line 16 should be returned?