Formula to exclude left value series from a dimension field

[Qlik17]

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.

[anat]

test:

LOAD *,Replace(Replace(Field1,'D',''),'E','') as Field2;
load * Inline [
Field1
XO24G001D

XO24G001E

XO24G001

XO24T001

XO24T001D

XO24T001E

];

[qv_testing]

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!

[BrunPierre]

This expression will extract the first 8 characters from the 'Lot' field.

=Mid(Lot,1,8)

[Qlik17]

Hi,
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
-

[qv_testing]

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)

[Rohan]

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.

[Chanty4u]

Try this

=IF(

    WildMatch(LOT, '*D') = 0 AND WildMatch(LOT, '*E') = 0,

    LOT

)

[Chanty4u]

Try this too.=IF(

    WildMatch(LEFT(LOT, len(LOT)-1), '*D') = 0 AND WildMatch(LEFT(LOT, len(LOT)-1), '*E') = 0,

    LOT

)