topic Ranking in order of priority in App Development

Ranking in order of priority

MattMika — Thu, 28 Jul 2022 12:37:42 GMT

Dear Community,

I have a "Planning" column which is causing duplicates, I need to prioritize the contents of this column which contains A , B and C such that A is stronger then B then C.

For exemple : if Planning contains A, B and C, then Planning take A , if B and C then Planning must take B...

I tried this but it didn't work :

if ( match(Planning,'A') and match ( Planning,'B'),'A',
if ( match(Planning,'A') and match ( Planning,'C'),'A',
if ( match(Planning,'B') and match ( Planning,'C'),'B'))) as Plan

Thanks in advance

Re: Ranking in order of priority

canerkan — Thu, 28 Jul 2022 13:27:39 GMT

Hi MattMika,

for this logic, you can simply use

If(Match(Planning, 'A') > 0, 'A', If(Match(Planning, 'B') > 0, 'B', 'C')) as Plan

Let me know if it helped

Regards

Can

Re: Ranking in order of priority

marcus_sommer — Thu, 28 Jul 2022 14:17:49 GMT

This couldn't work because it's evaluated on the record-level and here has Planning always a single value. This means you will need to aggregate the multiple Planning status-information against the needed dimensionality.

It might be done with concat() which returned a concatenated string which might be checked with match/wildmatch() within if-loops but easier could be just to use maxstring() or by using of appropriate dual() values for Planning min/max() or even sum() to get the wanted information. This may look like:

t: load AnyKey, maxstring(Planning) as MaxPlanning
from Source group by AnyKey;

- Marcus

Re: Ranking in order of priority

MattMika — Fri, 29 Jul 2022 08:11:04 GMT

Hi Canerkan,

It didn't work, this solution delete only the duplicate rows while i need to take only one value.

Regards,

Matt

Re: Ranking in order of priority

canerkan — Fri, 29 Jul 2022 10:44:39 GMT

Hi MattMika,

do you have any sample data you can provide by chance?

Re: Ranking in order of priority

MarcoWedel — Fri, 29 Jul 2022 19:22:21 GMT

what single value of "Planning" would you expect to match 'A' and 'B' at the same time?

Re: Ranking in order of priority

MattMika — Mon, 01 Aug 2022 09:07:11 GMT

Hi Canerkan,

Attached is an example of existing data and the result wanted!

Thanks in advance for your help

Re: Ranking in order of priority

MattMika — Mon, 01 Aug 2022 09:10:10 GMT

Hi Marco,

Attached an example that better expresses my need.

Thanks

Re: Ranking in order of priority

marcus_sommer — Mon, 01 Aug 2022 09:32:01 GMT

The results from your example are looking different to the described aim from above - in not getting the highest/latest status else the last/first one.

In this case you may use interrecord-functions like peek() or previous() within a properly sorted resident-load with an if-loop querying if the current record is a new one compared to the key-fields from the above record or not.

Beside this you could use an aggregation-load like above mentioned with first/last() or firstsortedvalue() as aggregation-function - just try it within a new app and your sample-data and if it worked like expected you transfer it into your origin app.

- Marcus

Re: Ranking in order of priority

canerkan — Mon, 01 Aug 2022 09:50:40 GMT

Hi MattMika,

maybe try the following script. In this Script the Rank goes from A2, A1, A3:

Table: LOAD [Item], [Store], Planning, Dual(Planning, If(Planning = 'A2', 1, If(Planning = 'A1', 2, 3))) as RankPlanning FROM [lib://Downloads/Sample.xlsx] (ooxml, embedded labels, header is 1 lines, table is Feuil1); Final: Load Item, Store, Min(RankPlanning) as RankPlanning Resident Table Group by Item, Store; Drop Table Table;

Let me know if it helped.

Regards,

Can

Re: Ranking in order of priority

MattMika — Mon, 01 Aug 2022 11:38:56 GMT

It worked as expected, Thank you for your help ✌️