Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
[Data 1]:
LOAD
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
Alt ([B Temp], 0) as
Resident
[Data 1];
DROP Table [Data 1];
Hi,
the sequence above is a portion in the middle, right ?
otherwise, what is the rule used to set 1 for reference:TH55M630QF ??
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
[Data 1]:
LOAD
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
Alt ([B Temp], 0) as
Resident
[Data 1];
DROP Table [Data 1];
Thank you ED, This works
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.