Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
];
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