Create a flag

[paulwalker]

Hi Community,

below is my table 

Load * Inline [
Name, Software, Status
Paul, Qlikview, Active
Paul, Qlik Sense, Active
Akram, Qlik Sense, Active
Edwerd, Spotfire, Active
Edwerd, Spotfire, In-Active
Ryan, Splunk, In-Active
Ryan, Tableau, In-Active
];
If user doesn't have active status, we have to tag close else Active. (I'm expecting output column like below)

Name	Software	Status	Output
Paul	Qlikview	Active	Active
Paul	Qlik Sense	Active	Active
Akram	Qlik Sense	Active	Active
Edwerd	Spotfire	Active	Active
Edwerd	Spotfire	In-Active	Active
Ryan	Splunk	In-Active	Closed
Ryan	Tableau	In-Active	Closed

Thanks in Advance!

[Prem0212]

T think this script will work for you.

Emp_Details:

Load *,
If(Status= 'Active', 'Active', 'Closed') as Output
Inline
[

Name, Software, Status
Paul, Qlikview, Active
Paul, Qlik Sense, Active
Akram, Qlik Sense, Active
Edwerd, Spotfire, Active
Edwerd, Spotfire, In-Active
Ryan, Splunk, In-Active
Ryan, Tableau, In-Active

];

[paulwalker]

Thanks for quick response,

this will not work. (It will give all in-Active to Closed and this will work only for Status column)

but my scenario is I have to group on Name level, let say Ryan doesn't have Active - it should tag Closed

Another Example:  Edwerd has Active and In-Active - it should tag to Active for both records

[Chanty4u]

Try this 

LOAD 

    Name, 

    Software, 

    Status, 

    If(Sum(If(Status='Active',1,0))>0, 'Active', 'Closed') AS Output

RESIDENT 

    [YourTable]

GROUP BY 

    Name, S

oftware;

[paulwalker]

I have tried with changed data 

ABC:
LOAD * INLINE[
Name, Software, Status
Paul, Qlikview, Active
Paul, Qlik Sense, Active
Akram ,Qlik Sense, Active
Edwerd ,Spotfire, Active
Edwerd ,ABC, In-Active
Ryan, Splunk, In-Active
Ryan, Tableau ,In-Active
];

LOAD Name,
Software,
Status, If(Sum(If(Status='Active',1,0))>0, 'Active', 'Closed') AS Output
Resident ABC
GROUP BY
Name, Software, Status;

drop table ABC;
exit script;

Edwerd should be Active 

[Kushal_Chawda]

@paulwalker  try below

Data:
Load * Inline [
Name, Software, Status
Paul, Qlikview, Active
Paul, Qlik Sense, Active
Akram, Qlik Sense, Active
Edwerd, Spotfire, Active
Edwerd, Spotfire, In-Active
Ryan, Splunk, In-Active
Ryan, Tableau, In-Active
];

Left Join(Data)
Load distinct Name,
              Status as Status_new
Resident Data
where Status='Active';

Final:
NoConcatenate
LOAD *,
     if(isnull(Status_new),'Closed',Status_new) as Status_Final
Resident Data;

Drop Table Data;

Drop Field Status_new;

[Sabrina_V]

Try this : 

DATA:
Load * Inline [
Name, Software, Status
Paul, Qlikview,Active
Paul, Qlik Sense,Active
Akram, Qlik Sense,Active
Edwerd, Spotfire,Active
Edwerd, Spotfire,In-Active
Ryan, Splunk,In-Active
Ryan, Tableau,In-Active
];

TEST:
MAPPING LOAD DISTINCT
Name,
Status as FlagActive
RESIDENT
DATA
WHERE
Status ='Active';

NoConcatenate
FINAL:
LOAD
*,
ApplyMap('TEST',Name,'Closed') as Output
RESIDENT
DATA;


DROP TABLE DATA;

And the result is : 

[maxgro]

DATA:
Load * Inline [
Name, Software, Status
Paul, Qlikview,Active
Paul, Qlik Sense,Active
Akram, Qlik Sense,Active
Edwerd, Spotfire,Active
Edwerd, Spotfire,In-Active
Ryan, Splunk,In-Active
Ryan, Tableau,In-Active
];

left join (DATA) LOAD
Name,
if(MinString(Status)='Active', 'Active', 'Closed') as Output
resident DATA
group by Name;