topic Re: SubField() Wildmatch in QlikView

SubField() Wildmatch

nigharikkha — Fri, 20 May 2022 12:23:21 GMT

Hi,

I am using SubField() while loading to get separate rows for a field separated by comma(,).

Ex: Sample data as below.

Devices | Slot | Value

-----------------------------

ABC1,ABC2,ABC3 | 1 | 4345

ABC2 | 3 | 3r545r3

ABC4,ABC7 | 5 | 3435r45

Load Script that I am using:

LOAD

SubField(Devices,',') as Device,

Slot,

Value

I need to add a condition to this load something like WildMatch(Device,'ABC1','ABC2','ABC3').

Can anyone help how this match condition be applied for a subfield?

Thanks

Re: SubField() Wildmatch

saranyadurai — Fri, 20 May 2022 12:45:49 GMT

Hi @nigharikkha ,

May be try like this,

LOAD

If(Wildmatch(SubField(Devices,','),'ABC1','ABC2','ABC3'),Your Output,0) as Device,

Slot,

Value

FROM XXX;

Hope this helps!

Re: SubField() Wildmatch

PrashantSangle — Fri, 20 May 2022 12:46:53 GMT

What is your expected output??

Re: SubField() Wildmatch

nigharikkha — Fri, 20 May 2022 12:51:33 GMT

@SaraK Hi I tried this but there the function does not allow subfield inside.

Re: SubField() Wildmatch

nigharikkha — Fri, 20 May 2022 12:53:02 GMT

I got the solution. I used preceding LOAD as below.

LOAD * where WildMatch(Device,'ABC1','ABC2','ABC3');

LOAD

SubField(Devices,',') as Device,

Slot,

Value

Re: SubField() Wildmatch

maxgro — Fri, 20 May 2022 12:53:17 GMT

You can use a preceding load

Data:
LOAD * INLINE [
Devices| Slot| Value
ABC1,ABC2,ABC3 | 1 | 4345
ABC2 | 3 | 3r545r3
ABC4,ABC7 | 5 | 3435r45
] (delimiter is '|');

Output:
LOAD
*
WHERE
WildMatch(Device,'ABC1','ABC2','ABC3');
LOAD
SubField(Devices,',') as Device,
Slot,
Value
RESIDENT Data;

Drop table Data;

Re: SubField() Wildmatch

nigharikkha — Fri, 20 May 2022 12:54:55 GMT

Thanks @maxgro