How to create a calculated field B with value 1 or 0, if the value in the filed A is repeated more than once

[Anonymous]

Hi Guys,

   i wanted to created a New field B with 1 or 0 , if the value in the field A is repeated more than once than 1 else 0.

Could someone help me with the script

A	B
CN7AOC61CF	0
5CD7233LRW	0
TH55M630QF	1
TH7831Q01T	1
CN2CS9QGBZ	1
5CD6493R5K	1
TH55M630QF	1
TH7831Q01T	1
CN2CS9QGBZ	1
5CD6493R5K	1
5CD6516707	0

Regards,

Karthik

[YoussefBelloum]

Hi,

the sequence above is a portion in the middle, right ?

otherwise, what is the rule used to set 1 for reference:TH55M630QF ??

[olivierrobin]

hello

I would  do :

1 - create a table with column A and count the number of times it appears in the file (you can transform this value as 0 or 1)

2 - load the table

3 - join the 1st table on key A

[ehilsinger · Accepted solution]

[Data 1]:

LOAD

as [A Text],

Dual (, RowNo ()) as

Inline

[

A

CN7AOC61CF

5CD7233LRW

TH55M630QF

TH7831Q01T

CN2CS9QGBZ

5CD6493R5K

TH55M630QF

TH7831Q01T

CN2CS9QGBZ

5CD6493R5K

5CD6516707

];

[Temp]:

LOAD

[A Text] as [TEMP.A],

Count ([A Text]) as [TEMP.Count]

Resident

[Data 1]

Group By

[A Text];

Left Join ([Data 1])

LOAD

[TEMP.A] as [A Text],

1 as [B Temp]

Resident

[Temp]

Where

([TEMP.Count] > 1);

DROP Table [Temp];

[Data]:

NoConcatenate

LOAD

as ,

Alt ([B Temp], 0) as

Resident

[Data 1];

DROP Table [Data 1];

[Anonymous]

Thank you ED, This works

[vishsaggi]

Try this?

Data:

LOAD * INLINE [

A

CN7AOC61CF

5CD7233LRW

TH55M630QF

TH7831Q01T

CN2CS9QGBZ

5CD6493R5K

TH55M630QF

TH7831Q01T

CN2CS9QGBZ

5CD6493R5K

5CD6516707

];

NoConcatenate

Ordered:

LOAD *, RowNo() AS RowNum

Resident Data

Order By A;

LEFT JOIN(Ordered)

LOAD *, IF(RowCnt = 1, 0, 1) AS B;

LOAD A,

     Count(RowNum) AS RowCnt

Resident Ordered

Group By A;

Drop Table Data;

Use straight table and add

Dim: A, RowNum

Expr: B

Follow the below screenshot.