Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

karthik123456
New Contributor

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

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

AB
CN7AOC61CF0
5CD7233LRW0
TH55M630QF1
TH7831Q01T1
CN2CS9QGBZ1
5CD6493R5K1
TH55M630QF1
TH7831Q01T1
CN2CS9QGBZ1
5CD6493R5K1
5CD65167070

Regards,

Karthik

1 Solution

Accepted Solutions
ehilsinger
New Contributor III

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

[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];

View solution in original post

5 Replies
YoussefBelloum
Esteemed Contributor

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

Hi,

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

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

olivierrobin
Valued Contributor II

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

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
New Contributor III

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

[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];

View solution in original post

karthik123456
New Contributor

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

Thank you ED, This works

vishsaggi
Esteemed Contributor III

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

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.

Capture1.PNG

Capture.PNG