Generate a list of unmatched values

[surajap123]

Hi Experts,

I want to generate a list of values that has no matching ID.
eg-

Map_Color:
Mapping
LOAD * INLINE [
Color, Fruit
Red, Apple
Black, Grapes
];

Map_Loc:
Mapping
LOAD * INLINE [
Loc, Desc
yy, US
aa, UK
];

  Stock:
LOAD *,
ApplyMap('Map_Color',Color,'Unknown:' &Color) as Fruit,
ApplyMap('Map_Loc',Loc,'Unknown:' &Loc) as Desc
INLINE [
ID, Color, Qty, Loc
1, Blue, 35, xx
2, Green, 36, yy
3, Red, 423, zz
4, Black, 13, aa
5, Orange, 23, bb
];

In a separate field I only want below output, as they are no corresponding match with lookup table.

Output Expected in each field--

Fruit
Blue
Green
Orange

Desc
xx
zz
bb

Please help!

[Anil_Babu_Samineni]

May be this for List box expression

=If(Not WildMatch(Color, 'Black','Red'), Color)

[trdandamudi]

Is a straight table is ok like below:

[Kushal_Chawda]

like this?

Map_Table:

Mapping

LOAD * INLINE [

    Color, Fruit

    Red, Apple

    Black, Grapes

];

Stock:

LOAD *,

  ApplyMap('Map_Table',Color,'NA') as Fruit

  INLINE [

    ID, Color, Qty

    1, Blue, 35

    2, Green, 36

    3, Red, 423

    4, Black, 13

    5, Orange, 23

];

Left Join(Stock)

LOAD ID,

          Color as ColorNew

Resident Stock

where Fruit='NA';

[surajap123]

Thanks Everyone for the suggestions.

@kushal chawda - My main requirement is to create a error table, that stores various field values that has no matching ID's, as shown in above example.

So I have multiple fields (like 'Color') in my data model, which I want to validate.

So I want to create a separate table and don't want to join it.

Could you help me!

[Kushal_Chawda]

So do you want that table separately? Not linked to others

[prieper]

add the below

Unknown:

    LOAD Color    AS UnknownColor RESIDENT Stock WHERE WILDMATCH(Fruit, 'Unknown*');

HTH Peter

[surajap123]

Yes, as separate island table.

[surajap123]

Hi Peter,

I think, I cannot use where condition on single field, as i have multiple fields that has to go into separate table.

[prieper]

This was not said before ...
Try with CONCATENATE and build the Error-table one-by-one

Peter