topic Re: Can Iterations be done for Wildmatch funciton? in QlikView

Can Iterations be done for Wildmatch funciton?

Fri, 06 Feb 2015 05:39:41 GMT

Hello all,

I have the following problem in my script. I have two tables

Table 1:

Key	Name
100	‘PjtXYZ #NLPOAC, Tra #NLPSHX'

Table 2:

Key	Partner
100	HP
100	SAP
100	Microsoft

Now my second table (partner) should look like the below

Key	Partner	NLP	JSO
100	HP	#NLPOAC	HP Kit
100	SAP	#NLPSHX	SAP Run
100	Microsoft

In table 1, under 'Name' field, I have 2 '#NLP strings'. I want to identify both strings one after another.

Is there any iterative function available in qlikview for wildmatch functions so that strings are searched one after another.

Each string should have one partner.

Kindly help me on this.

Thanks in advance,

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

tresB — Fri, 06 Feb 2015 05:48:29 GMT

There would be some work around. Could you explain - how you associate #NPLOAC with HP and #NLPSHX with SAP? Is it the order?

Re: Can Iterations be done for Wildmatch funciton?

Fri, 06 Feb 2015 05:54:24 GMT

Thanks for your quick repli!!

There is no specific order. In table 2, first partner is 'HP'. So the first '#NL' string should be tagged to HP.

At the end I should have #NL string for all the partners available.

We have 3 Keys with different Partners.

So, each Partner should get #NL string.

Hope this helps.

Re: Can Iterations be done for Wildmatch funciton?

tresB — Fri, 06 Feb 2015 06:14:19 GMT

May be like attached qvw?

Re: Can Iterations be done for Wildmatch funciton?

Fri, 06 Feb 2015 11:17:25 GMT

thanks, this is what I wanted.

i tried to do the same in the attached qvw file, I dont get the exact result.

Could you please advise where I have gone wrong?

Thanks in advance

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

tresB — Mon, 09 Feb 2015 04:58:59 GMT

Change the SubField separator from comma to semi-colon ':' and try like:

SubField(Name, ':') as Name

Re: Can Iterations be done for Wildmatch funciton?

Mon, 09 Feb 2015 06:54:59 GMT

Hi Deepak,

Sample application is attached here.

Kindly help me on this.

Thanks,

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

Mon, 09 Feb 2015 06:58:47 GMT

Hello Tresesco,

I still get the same.

Kindly have a look at the attached screenshot.

Thanks,

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

Mon, 09 Feb 2015 07:10:06 GMT

Is there any While statement wherein we can use IterNo() to achieve the result.

Load
Key,

if(<There is an IterNo() occurrence of *#NL?B* in the Name>,

<Distill this IterNo()’s occurrence from the Opty Name>) as NLPB
Resident Table1;

While <there are more occurrences of *#NL?B* in the Name>;

Thanks,

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

tresB — Mon, 09 Feb 2015 07:11:59 GMT

Try to share your sample data excel.

Re: Can Iterations be done for Wildmatch funciton?

Mon, 09 Feb 2015 07:19:59 GMT

Hello,

PFA

Thanks

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

tresB — Mon, 09 Feb 2015 07:29:51 GMT

Try :

'#NLP'&SubField(Name, '#NLP') as Name

Re: Can Iterations be done for Wildmatch funciton?

Mon, 09 Feb 2015 08:59:11 GMT

When I used

'#NLP'&SubField(Name, '#NLP') as Name

I get like the below

Key Partner Tag

101 HP #NLPBACS

102 SAP #NLPBNDD #NLPBASD

102 Oracle #NLPBNDD #NLPBASD

Both #NL in the same line

Thanks,
Vidhya

Re: Can Iterations be done for Wildmatch funciton?

tresB — Mon, 09 Feb 2015 09:12:14 GMT

In your sample excel there is no Oracle. So it's hard to understand what you mean.

Re: Can Iterations be done for Wildmatch funciton?

Mon, 09 Feb 2015 09:25:49 GMT

hello,

I have many combinations of #NL strings in my data model.

When I copied some of those examples, I get different results.

I have attached my work here

Thanks,

Vidhya

Re: Can Iterations be done for Wildmatch funciton?

tresB — Mon, 09 Feb 2015 09:42:36 GMT

You need to drop the temporary table and some more basic corrections highlighted.

T1:
Load
SLID,
Mid([Opty Name],Index([Opty Name],'#NL')) as Name,
RecNo()
FROM
[JSO.xlsx]
(ooxml, embedded labels, table is Sheet1);

T2:
Load SLID,
//'#NL'& SubField(Name, '#NL') as NLPB
'#NLP'&SubField(Name, '#NLP') as Name

// SubField(Name, '#NL') as Name

//SubField(Name, ',') as Name
Resident T1;

left Join
LOAD
RecNo(),
SLID,
Partner,
Tagging
FROM
[JSO.xlsx]
(ooxml, embedded labels, table is Sheet2);

Drop Table T1;