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;
If you want to load only these 3 lines as output table, instead of left join, use inner join.