Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following table as Input:
Item id | Order ID | First letter of Order ID |
1 | 1DTE1 | 1 |
1 | 3ATM1 | 3 |
2 | 2PTE3 | 2 |
2 | DTA | D |
2 | 3PTW2 | 3 |
3 | ADF | A |
3 | DSD | D |
3 | SDF | S |
Rules to be applied on this:
1. If first letter of a order id is a numeric for a given Item Id then select the maximum value row;
2. If first letter of a order id is a numeric or character then take any of the character based row.
Expected Result table:
Item id | Order ID | First letter of Order ID |
1 | 3ATM1 | 3 |
2 | DTA | D |
3 | ADF | A |
Hi @rammarthi
May be, try like below
Source:
LOAD *, if(IsNum(FirstLetter), 1, 0) as Num, Ord(FirstLetter) as LetterValue INLINE [
Itemid, OrderID, FirstLetter
1, 1DTE1, 1
1, 3ATM1, 3
2, 2PTE3, 2
2, DTA, D
2, 3PTW2, 3
3, ADF, A
3, DSD, D
3, SDF, S
];
Temp:
Load Itemid, MaxNum as FirstLetter, 1 as ResultFlag where Flag = 1;
Load Itemid, If(Count(Itemid) = Sum(Num), 1, 0) as Flag, Max(FirstLetter) as MaxNum Resident Source Group by Itemid;
Concatenate
Load Itemid, FirstSortedValue(FirstLetter, LetterValue) as FirstLetter, 1 as ResultFlag Resident Source Where Num = 0 Group by Itemid;
Left Join(Source)
Load * Resident Temp;
DROP Table Temp;
DROP Field Num, LetterValue;
o/p:
If you want to load only these 3 lines as output table, instead of left join, use inner join.