- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Formula to exclude left value series from a dimension field
Hello,
I have a dimension called Lot that list the following series:
XO24G001D
XO24G001E
XO24G001
XO24T001
XO24T001D
XO24T001E
I would to exclude D and E from the list. I tried the expression
=IF(MID(LOT,5,1)='T' AND left(LOT,1)<>'D' OR LEFT(LOT],1)<>'E' ,
LOT)
but, it did not seem to filter out D and E.
- Subscribe by Topic:
-
Chart
-
Client Managed
-
Data Load Editor
-
Developers
-
expression
-
filter
-
General Question
-
Script
-
Section Access
-
Set Analysis
-
Variables
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
test:
LOAD *,Replace(Replace(Field1,'D',''),'E','') as Field2;
load * Inline [
Field1
XO24G001D
XO24G001E
XO24G001
XO24T001
XO24T001D
XO24T001E
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This would work..
//LOAD * where not WildMatch(LOT,'*E', '*D'); // this will exclude records from data
LOAD *, IF(not WildMatch(LOT,'*E', '*D'), 1, 0) as ExcludeFlag; // this can create flag
LOAD * INLINE [
LOT
XO24G001D
XO24G001E
XO24G001
XO24T001
XO24T001D
XO24T001E
];
If you want in expression.. you can use below one
ONLY({<LOT-={"*D","*E"}>}LOT)
I hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This expression will extract the first 8 characters from the 'Lot' field.
=Mid(Lot,1,8)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your prompt answer.
I still want to load the information because I will use it in a different table. I just would like to be able to filter it out through the "dimension" option, if possible. Please let me know if you this is doable. Also, I cannot use a wildcard because the characters I need to filter out might be in the middle of the number. For example, the number could look like this XO23D025D or like this XO23D025DE. In this last number I would need to filter out the whole number that includes DE at the end, and not only D. I created an example of the table below. I hope that this makes things more clear.
Number Listed on Table
Number should show on table
XO23D023
XO23D023
XO23D023D
-
XO23D023DG
-
XO23T001
-
XO23T001A
XO23T001A
XO23T001AB
-
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This will work all the cases.
//LOAD * where not WildMatch(LOT,'*E*', '*D*'); // this will exclude records from data
LOAD *, IF(not WildMatch(LOT,'*E*', '*D*'), 1, 0) as ExcludeFlag; // this can create flag
LOAD * INLINE [
LOT
XO23D023
XO23D023
XO23D023D
XO23D023DG
XO23T001
XO23T001A
XO23T001A
XO23T001AB
];
If you want in expression.. you can use below one
ONLY({<LOT-={"*D*","*E*"}>}LOT)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Qlik17 ,
Try this :
{<Lot-={"????T*D","????T*E"}>}
in your set & check. This will check if T is present at the 5th position & if the string ends in D or E.
Regards,
Rohan.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
=IF(
WildMatch(LOT, '*D') = 0 AND WildMatch(LOT, '*E') = 0,
LOT
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this too.=IF(
WildMatch(LEFT(LOT, len(LOT)-1), '*D') = 0 AND WildMatch(LEFT(LOT, len(LOT)-1), '*E') = 0,
LOT
)