Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Edvin
Creator
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]

 

Thank you in advance.

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

9 Replies
Edvin
Creator
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]))

 

Edvin
Creator
Creator
Author

Guys, I'm really desperate..

 

sunny_talwar

May be this

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

Tested it in QlikView and it seems to be working

image.png

Edvin
Creator
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?

 

LOAD

"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.

sunny_talwar

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)
Edvin
Creator
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.

 

sunny_talwar

This should work

Table:
LOAD * INLINE [
    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)
LOAD [UNIQUE ID],
	 Count(If(TypeCode = 'KTIS' and SubItemCode = 'CAR', [UNIQUE ID])) as Count
Resident Table
Group By [UNIQUE ID];

FinalTable:
LOAD *,
	 If(Count >= 1 and TypeCode = 'BRI', 'NOT CAR', SubItemCode) as OUTPUT
Resident Table;

DROP Table Table;
Edvin
Creator
Creator
Author

I'm waiting for the day to be as smart mf as you.

Big appreciation!