New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for
Did you mean:
Creator

Wildmatch and then replacing value

Hello, guys.

I have a problem, that when I use wildmatch it finds what I need, but I need to replace some values then too.

Here's an example:

UNIQUE ID    TypeCode    SubItemCode    Output

123                   BRI                 CAR                        NOT CAR

123                   KTIS               CAR                        CAR

598                   KTIS               CAR                        CAR

598                   KTIS               CAR                        CAR

103                   TRIS               NOT CAR              NOT CAR

578                   KTIS               CAR                        CAR

999                   BRI                 NOT CAR               NOT CAR

999                   KTIS               CAR                         CAR

752                   BRI                  CAR                         CAR

So I WildMatch() 'CAR' and 'NOT CAR'. What I need, if I match 'CAR' for [TypeCode]='KTIS' and 'BRI', I need to display only 'CAR' for [TypeCode]='KTIS'. After I WildMatch() I need to replace all 'CAR' to 'NOT CAR' for all 'BRI' rows IF there's a 'CAR' in [TypeCode]='KTIS'.

I tried to apply some logic (I know it can be done with set analysis, it's just a logic approach):

if(count([SubItemCode]='CAR')>1 and count([SubItemCode])>1 and [SubItemCode]='CAR','NO CAR',[SubItemCode]

1 Solution

Accepted Solutions
MVP

You can try this for dimension

``````Aggr(
If(Count(TOTAL <[UNIQUE ID]> {<TypeCode = {'KTIS'}, SubItemCode = {'CAR'}>} [UNIQUE ID]) and TypeCode = 'BRI', 'NOT CAR', SubItemCode)
, [UNIQUE ID], TypeCode, SubItemCode)``````
9 Replies
Creator
Author

Anyone guys?

It looks pretty simple, but I can't seem to do it myself..

If I match 2 'CAR' for same UNIQUE ID but with two different [TypeCode], and one of the [TypeCode] is 'KTIS', I need to leave only 'CAR' for that [TypeCode]

I try:

=if(aggr(sum(if([SubItemCode]='CAR',1,0)),[UNIQUE ID])>1, aggr(IF([TypeCode]='KTIS','CAR','NOT CAR'),[UNIQUE ID]))

Creator
Author

Guys, I'm really desperate..

MVP

May be this

``If(Count(TOTAL <[UNIQUE ID]> {<TypeCode = {'KTIS'}, SubItemCode = {'CAR'}>}[UNIQUE ID]) and TypeCode = 'BRI', 'NOT CAR', SubItemCode)``
MVP

Tested it in QlikView and it seems to be working

Creator
Author

Sunny, you are a god :).

It's just that it's only working by creating a measure, not in a dimension.

Maybe it's possible to do in back-end?

"UNIQUE ID",

"TypeCode",

if(wildmatch("TypeCodeItem",'*pakait*','*pakat*','*nuom*','*pkait*','*nom*')>0 AND wildmatch("TypeCode",'KTIS','BRI','TRIS')>0,'CAR','NOT CAR') as "SubItemCode"

FROM ....

I search 'CAR' 'NOT CAR' based on field "TypeCodeItem" just that one more condition I need to met, that 'CAR', 'NOT CAR' need to search only in 'KTIS','BRI','TRIS' TypeCodes.

Really appreciate the help.

MVP

You can try this for dimension

``````Aggr(
If(Count(TOTAL <[UNIQUE ID]> {<TypeCode = {'KTIS'}, SubItemCode = {'CAR'}>} [UNIQUE ID]) and TypeCode = 'BRI', 'NOT CAR', SubItemCode)
, [UNIQUE ID], TypeCode, SubItemCode)``````
Creator
Author

One more thing @sunny_talwar , I'm creating a pivot table, when I try filtering on this dimension whether a 'CAR' or 'NOT CAR' it selects both of the values.

If it's possible to do in script (back-end) would be awesome.

MVP

This should work

``````Table:
UNIQUE ID, TypeCode, SubItemCode
123, BRI, CAR
123, KTIS, CAR
598, KTIS, CAR
598, KTIS, CAR
103, TRIS, NOT CAR
578, KTIS, CAR
999, BRI, NOT CAR
999, KTIS, CAR
752, BRI, CAR
];

Left Join (Table)
Count(If(TypeCode = 'KTIS' and SubItemCode = 'CAR', [UNIQUE ID])) as Count
Resident Table
Group By [UNIQUE ID];

FinalTable: