Need to get Employee data based upon the priority in script

[praveenkumar_s]

Hi friends,

I need to get the Employee status based upon the priority during the load script as attached excel for your reference and screenshot too. Kindly share your knowledge as it would be very helpful.

[tresesco · Accepted solution]

t1:

Load * Inline [

Emp_id, "priority type", status

1, a, active

1, b, inactive

1, c, absent

2, b, inactive

2, c, absent

3, a, active

3, c, absent

];

t2:

Load

       Emp_id,

       FirstSortedValue("priority type", Ord("priority type")) as "priority type",

       FirstSortedValue(status, Ord("priority type")) as CurrentStatus

Resident t1 Group by Emp_id;

Drop Table t1;

[praveenkumar_s]

Hi friends,

I have found the script for above question while im trying and pasted below. Please share if any quick way to achieve it.

Priority:

LOAD Emp_id,

     [priority type],

     if([priority type]='a','1',if([priority type]='b','2',if([priority type]='c','3'))) as prior_order,

     status

FROM

(ooxml, embedded labels, table is [Source table]);

NoConcatenate

new:

load Emp_id,MinString(prior_order) as prior_order resident Priority Group by Emp_id;

inner join

load * resident Priority;

     drop table Priority;

[praveenkumar_s]

hi tresesco,

Thank you very much for your answer .

The above script which you gave was working correctly. but small thing which i need to clarify is that a,b and c are in alphabetical order so that above script was working fine. suppose b was my first priority means , how can i get that ?

[tresesco]

Then you can use an IF statement or match() to assign it an incremental numeric values (or, if the priority field has many values, you may want to maintain a mapping table) , and then use firstsortedvalue()..