Pick priority code for specific status

[rob_vander]

Hi All,

I have below scenario for which I am looking for script solution. Data looks like below

Load * Inline [
ID, Status, Code
2345,Released,AA
2345,Released,DD
2345,Released,BB
1560,Released,BB
1560,Released,CC
1245,Pending,AA
1245,Pending,BB
1245,Released,CC
2000,Pending,AA
2000,Pending,CC
2000,Pending,BB ]

Basically, for each ID I need to Check if status is 'Released', then I need to pick Code based on priority of the Code. Priority of Code is AA->BB->CC->DD

ID 2345,   Pick 'AA' based on Priority for Status='Released'

ID 1560,  Pick 'BB' based on Priority for Status='Released'

ID 1245,  Pick 'CC' based on Priority for Status='Released' 

ID 2000,  Pick 'AA' . Here we don't have status ='Released', so pick the Highest Priority Code for any status

Output :

ID	Required_Code
2345	AA
1560	BB
1245	CC
2000	AA

Note:  In real data I have more than 10 Status and more than 20 Codes, but priority Codes are only Four. Also, this is just sample Codes. Actual code will be 3 digits like COD,DAC,CAD..etc

[BrunPierre]

Hi, maybe not so elegant but a possible solution.

Tmp:
Load * Inline [
ID, Status, Code
2345,Released,AA
2345,Released,DD
2345,Released,BB
1560,Released,BB
1560,Released,CC
1245,Pending,AA
1245,Pending,BB
1245,Released,CC
2000,Pending,AA
2000,Pending,CC
2000,Pending,BB ];

//Temporary table to help distinguish IDs with only pending status from IDs with mix status
Tmp2:
 LOAD ID as ReleasedIDs,
 MinString(Code) as Required_Code

Resident Tmp
Where Match(Status,'Released')
Group by ID;

//Extract only Pending records
Pending:
LOAD ID,
MinString(Code) as Required_Code

Resident Tmp
Where not Match(Status,'Released') 
and
not Exists(ReleasedIDs,ID) //To get only IDs with pending status
Group by ID;

Concatenate //merging released records to pending records
Released:
 LOAD ID,
 MinString(Code) as Required_Code

Resident Tmp
Where Match(Status,'Released')
Group by ID;

DROP Tables Tmp, Tmp2;

EXIT SCRIPT;

[rob_vander]

@BrunPierre  Thanks for your reply. But, minstring won't work here because this is just example.. but in my data code will be like AOD, CAD,DAC..... etc..  Also, I don't want to only two Column in final table. I want all data with Required Code as another column

[Kushal_Chawda · Accepted solution]

@rob_vander  Assign Rank to Status and Code so that on sorting for each ID you will get first value as required code. I have also put some dummy values to test some scenarios

Data:
Load *,
if(Status='Released',1,2) as Status_Rank 
Inline [
ID, Status, Code
2345,Released,AA
2345,Released,DD
2345,Released,BB
1560,Released,BB
1560,Released,CC
1245,Pending,AA
1245,Pending,BB
1245,Released,CC
2000,Pending,AA
2000,Pending,CC
2000,Pending,BB 
3000,Pending,AA
3000,Pending,CC
3000,In Progress,BB
3000,Released,CC
3000,Released,BB];

// Assign Rank to Code , Change your Code value accordingly.
Left Join(Data)
Load Code,
     if(Code='AA',1,if(Code='BB',2,if(Code='CC',3,
     if(Code='DD',4,AutoNumber(Code)+4)))) as Code_Rank;
Load FieldValue('Code',RecNo()) as Code
AutoGenerate FieldValueCount('Code');

Left Join(Data)
Load ID,
     FirstValue(Code) as Required_Code
Resident Data
Group by ID
Order by ID,Status_Rank,Code_Rank;

Drop Fields Code_Rank,Status_Rank;

[rob_vander]

@Kushal_Chawda  thanks. it works

[Qrishna]

Probably another way to do it.

Tmp:
Load * Inline [
ID, Status, Code
2345,Released,AA
2345,Released,DD
2345,Released,BB
1560,Released,BB
1560,Released,CC
1245,Pending,AA
1245,Pending,BB
1245,Released,CC
2000,Pending,AA
2000,Pending,CC
2000,Pending,BB ];

left join(Tmp)
priority:
Load * Inline [
Status, Code, Priority
Released, AA, 1
Released, BB, 2
Released, CC, 3
Released, DD, 4
Pending, AA, 5
Pending, BB, 6
Pending, CC, 7
Pending, DD, 8 ];

left join(Tmp)
Load ID, Min(Priority) as Max_Priority
Resident Tmp
Group by ID;

NoConcatenate
Dat:
Load ID, Status, Code
Resident Tmp
Where Priority = Max_Priority;
Drop Table Tmp;