Selecting Columns based on If Condition

[rammarthi]

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

[MayilVahanan]

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.