Rank and sort the data in a field in load script

[umashankarus]

Hi Experts,

I have a source data which contains the 3 columns

ID1    ID2     STATUS

1        100     Active-10/1/2018 5:07:09 PM-SVEMU;PenDisc-10/8/2015 11:49:56 AM-ADMIN;Active-10/1/2010 5:03:43 PM-SVEMU

2        101     Active-10/1/2019 5:07:09 PM-SVEMU;PenDisc-10/8/2017 11:49:56 AM-ADMIN;Active-1/1/2015 5:03:43 PM-SVEMU

3        100    Active-10/1/2019 5:07:09 PM-SVEMU

In the above example (row 1 and 2), there are three sets of data for 'STATUS' column separated by ';'

In real data set, this count varies between 1 to n

And I need the data in the status column to be sort based on the 'date' parameter within

So, the expected output is as below (data sort by 'date' value within the status column)

ID1    ID2     STATUS

1       100     Active-10/1/2010 5:03:43 PM-SVEMU;PenDisc-10/8/2015 11:49:56 AM-ADMIN;Active-10/1/2018 5:07:09 PM-SVEMU;

2       101     Active-1/1/2015 5:03:43 PM-SVEMU;PenDisc-10/8/2017 11:49:56 AM-ADMIN;Active-10/1/2019 5:07:09 PM-SVEMU

3       100    Active-10/1/2019 5:07:09 PM-SVEMU

Can we rank the data based on 'date' value within the status column and achieve the expected output

Please provide some inputs

Thanks,

Umashankar

[dplr-rn]

nothing straightforward comes to mind.

Only thing i can think use subfield function and split it into multiple rows and then into multiple columns

i.e. result would be soemthing like below

1        100     Active        10/1/2018 5:07:09 PM        SVEMU

1        100    PenDisc     10/8/2015 11:49:56 AM      ADMIN

then sort and combine as needed. little round about fashion but should work

[StarinieriG]

Hi,

try something like this

LOAD
ID1,
ID2,
Concat(test1,';',TextBetween(SubField(STATUS,';'),'-',' ')) AS STATUS
Group By
ID1,
ID2
;
LOAD *,
SubField(STATUS,';') as test1
;
LOAD * Inline [
ID1,ID2,STATUS
1,100,Active-10/1/2018 5:07:09 PM-SVEMU;PenDisc-10/8/2015 11:49:56 AM-ADMIN;Active-10/1/2010 5:03:43 PM-SVEMU
2,101,Active-10/1/2019 5:07:09 PM-SVEMU;PenDisc-10/8/2017 11:49:56 AM-ADMIN;Active-1/1/2015 5:03:43 PM-SVEMU
3,100,Active-10/1/2019 5:07:09 PM-SVEMU
];

[Brett_Bleess]

Umashankar, did either of the posts get you what you needed?  If so, please be sure to use the Accept as Solution button on the post(s) that actually helped you resolve things.  If you did something different, consider posting that and then mark it in the same way as above.  If you are still working upon things, leave an update on where you stand and perhaps someone else can leave an idea or two for you.  It would likely be helpful if you can attach an example app on this one as well if you do need further help.

Regards,
Brett