Wildmatch and then replacing value

[Edvin]

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.

[Edvin]

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]

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

[Edvin]

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 · Accepted solution]

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]

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]

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

Big appreciation!