Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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*');
May be this for List box expression
=If(Not WildMatch(Color, 'Black','Red'), Color)
Is a straight table is ok like below:
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';
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!
So do you want that table separately? Not linked to others
add the below
Unknown:
LOAD Color AS UnknownColor RESIDENT Stock WHERE WILDMATCH(Fruit, 'Unknown*');
HTH Peter
Yes, as separate island table.
Hi Peter,
I think, I cannot use where condition on single field, as i have multiple fields that has to go into separate table.
This was not said before ...
Try with CONCATENATE and build the Error-table one-by-one
Peter