Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
surajap123
Creator III
Creator III

Generate a list of unmatched values

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!

1 Solution

Accepted Solutions
surajap123
Creator III
Creator III
Author

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*');

View solution in original post

18 Replies
Anil_Babu_Samineni

May be this for List box expression

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
trdandamudi
Master II
Master II

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
Creator III
Creator III
Author

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
Master II
Master II

add the below

Unknown:

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

HTH Peter

surajap123
Creator III
Creator III
Author

Yes, as separate island table.

surajap123
Creator III
Creator III
Author

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
Master II
Master II

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

Peter