Flag in script to show when the count of x is >1 at x_Location

[Anonymous]

I would like to create a flag in the script that shows where the count of IP_Name is >1 at any distinct IP_Location.  (Both IP_name and IP_Location are in the same table S_IP)

[swuehl]

You probably need to use the aggregation function count() in your load script, thus you need a group by statement, something like ( I join the result back to your original table, not sure if this is what you want:

S_IP:

LOAD

IP_NAME,

IP_LOCATION

...

FROM ...;

LEFT JOIN (S_IP) LOAD

IP_LOCATION,

if(count(IP_NAME),1,0) as IP_NAME_COUNT_FLAG

resident S_IP group by IP_LOCATION;

[Anonymous]

Thanks, but it always come back as true?  I tried it your way and

if(count(Distinct IP_NAME >1),1,0 as Flag_Dupe

I want every distinct  IP_Name that exists more than once at a distinct IP_Location to be flagged

[Anonymous]

IP_Location     IP_Name                        IP_Serial_Lot_Number     FLAG_DUPE 10193_008_2     Hexapod Evo Module             002990      10193_008_2     XVI KV Generator             Serialized-GOL                      1 10193_008_2     iViewGT aSi Panel (MV)     Serialized-GOL                   1 10193_008_2     iViewGT aSi Panel (MV)     3375                                      1 10193_008_2     XVI KV Generator             G30310                              1   The first three columns come from the table I am loading, the fourth one (FLAG_DUPE) is the one I would like to create.

[swuehl]

Sorry, it's hard for me to read your table, I tried to parse in it an INLINE version, comma separated and added an flag creation. Maybe like this?

IP:

LOAD * INLINE [

IP_Location,     IP_Name,  IP_Serial_Lot_Number

10193_008_2,     Hexapod Evo Module,             002990 ,

10193_008_2 ,    XVI KV Generator ,            Serialized-GOL

10193_008_2,     iViewGT aSi Panel (MV),     Serialized-GOL

10193_008_2,     iViewGT aSi Panel (MV),     3375      

10193_008_2,     XVI KV Generator,             G30310    

];

left join LOAD IP_Location, IP_Name, if (count( IP_Name)>1,1,0) as DUPE Resident IP Group by IP_Location, IP_Name;

[Anonymous]

IP_Location                    IP_Name               Flag_Dupe

12345                              iViewGT               1

12345                              iViewGT               1

12345                              Hexapod              

12345                              XVI                        1

12345                              XVI                        1

Formatting went berserk

[Anonymous · Accepted solution]

Thank you so much swuehl.  Your example worked perfectly and I applied.  Very appreciated!

[Anonymous]

you rock!