Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
File ID | Indicator1 | Indicator2 | Indicator3 |
ABC | NA | y | NA |
cde | G | NA | NA |
efg | na | na | R |
I want to display 2 columns FILE ID and RGB
File ID RGB
ABC y
CDE G
EFG R
Each file ID has to pick the Indicator with R/Y/G by ignoring NA.hOW CAN i DO THAT ?tHANK YOU.
Hi,
You can try this.
Data:
LOAD * inline [
FileID, Indicator1, Indicator2, Indicator3
ABC, NA, y, NA
cde, G, NA, NA
efg, na, na, R
];
Load FileID as FID, PurgeChar(Indicator1&'-'&Indicator2&'-'&Indicator3,'-NAna') as New
Resident Data;
Regards,
Kaushik Solanki
Hi,
You can try this.
Data:
LOAD * inline [
FileID, Indicator1, Indicator2, Indicator3
ABC, NA, y, NA
cde, G, NA, NA
efg, na, na, R
];
Load FileID as FID, PurgeChar(Indicator1&'-'&Indicator2&'-'&Indicator3,'-NAna') as New
Resident Data;
Regards,
Kaushik Solanki
A very straightforward solution but maybe not the best:
SingleColumn:
LOAD UPPER([File ID]) AS [FILE ID],
if (UPPER(Indicator1) <> 'NA', Indicator1,
if (UPPER(Indicator2) <> 'NA', Indicator2, Indicator3)) AS RGB
RESIDENT OriginalTable;
BTW: watch your names. Field names are case-sensitive. Your OP contains three different spellings for field File ID.
Awesome Kaushik.This did the magic.Thank you and I appreciate your time