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!

[surajap123]

I have updated my initial post with 2 fields and also updated the app.

sorry, i am not sure how to concatenate each field. Could you help me.

[Kushal_Chawda]

try 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

];

Qualify *;

Unknown:

noconcatenate

LOAD *

Resident Stock

where Fruit='NA';

Unqualify *;

[prieper]

Try

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

Peter

[surajap123]

Hi Perer,

The concatenate approach has nulls. I tired to load the error fields into tablebox and straight table, so see if i can remove the nulls but doesn't help.

Can you suggest any other approach.

[prieper]

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


CONCATENATE 
LOAD
  Desc AS Unknown
RESIDENT 
   Stock 
     WHERE 
      WILDMATCH(Desc, 'Unknown*');  

[surajap123]

Thanks for the script, but we cannot keep all the data in one field(Unknown).

The purpose of error report is to inform the database team, about what are the value in each field that fail the validation check(which is unknown:... value returned by applymap).

[prieper]

a little bit of own thinking might be invested by you as well?

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


CONCATENATE 
LOAD
  'Desc'    AS Field,
  Desc         AS Unknown
RESIDENT 
   Stock 
     WHERE 
      WILDMATCH(Desc, 'Unknown*'); 

[effinty2112]

Hi Suraj,

Add two listboxes with expressions:

=Aggr(DISTINCT only({$<Desc = {"Unknown*"}>}Color),Color)

and

=Aggr(DISTINCT only({$<Desc = {"Unknown*"}>}Loc),Loc)

Cheers

Andrew

[surajap123 · Accepted solution]

I have used row() as a key field and joined the rest of the tables to it, to form a single table with fields side by side which form a error table.

ErrorTable:  

LOAD 

Rowno()          as Row,

   Color

RESIDENT 

   Stock 

     WHERE WILDMATCH(Fruit, 'Unknown*');

join(ErrorTable)

LOAD

Rowno()          as Row,

  Loc

RESIDENT 

   Stock 

     WHERE 

      WILDMATCH(Desc, 'Unknown*');