Ranking in order of priority

[MattMika]

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 

[canerkan]

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

[marcus_sommer]

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

[MattMika]

Hi Canerkan, 

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

Regards, 

Matt

[canerkan]

Hi MattMika,

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

[MarcoWedel]

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

[MattMika]

Hi Canerkan, 

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

Thanks in advance for your help 

[MattMika]

Hi Marco, 

Attached an example that better expresses my need. 

Thanks

[marcus_sommer]

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

[canerkan · Accepted solution]

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