Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
rammarthi
Creator
Creator

Selecting Columns based on If Condition

I have the following table as Input:

Item idOrder IDFirst letter of Order ID
11DTE11
13ATM13
22PTE32
2DTAD
23PTW23
3ADFA
3DSDD
3SDFS

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 idOrder IDFirst letter of Order ID
13ATM13
2DTAD
3ADFA
Labels (1)
1 Reply
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:

MayilVahanan_0-1618307592167.png

 

If you want to load only these 3 lines as output table, instead of left join, use inner join.

Thanks & Regards,
Mayil Vahanan R