topic Re: Scripting Help in QlikView

Scripting Help

vanderson009 — Mon, 07 Jul 2014 12:15:15 GMT

Hello guys,

I have 2 tables like below.

TableA :

Column1	Column2	Comment

Comment Column includes some Comment Sentence. Comment Sentence having some words like Column "Word" from TableB.

Those are having Word as frustrating or frustrated or frustration where i have to apply FLAG as CE negative,

Those are having Word as efficient or prompt or satisfied where i have to apply FLAG as CE positive and so on...

But the thing is that their is no linking between TableA and TableB.

TableB:

Tags	Words
CE negative	frustrating, frustrated, frustration
CE positive	efficient, prompt, satisfied
Staff poor	friendlier, rude, not pleasant

Please help me for this task....

-- Thanks and Regards,

Villyee Anderson

Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 12:21:30 GMT

Maybe you could load table B into variables and use the wildmatch() function to set the flags

Re: Scripting Help

vanderson009 — Mon, 07 Jul 2014 12:25:09 GMT

Hi Richard,

Thanks for your reply, but how can i store table into variable.

Is it possible to store multiple values in to variable.

-- Regards,

Villyee

Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 12:31:50 GMT

You could use this script:

TableB:

Load

Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 12:39:22 GMT

Actully WildMatch won't do this, I think you have to split up the words into separate variables and check each one using index() or substringcount() which could be handy to count the number of times you find the word.

How many Tags and words do you have?

Richard

Re: Scripting Help

vanderson009 — Mon, 07 Jul 2014 12:42:44 GMT

Hi Richard,

When i reloaded the script, it gives me error like as...

-- Regards,

Villyee

Re: Scripting Help

maleksafa — Mon, 07 Jul 2014 12:45:12 GMT

i think you need to transform the Table B to one word per line so for example

Tags	Words
CE negative	frustrating
CE negative	frustrated
CE negative	frustration

and then use the VLookup function on tableA field Comment to match with Field Words from TableB

Re: Scripting Help

vanderson009 — Mon, 07 Jul 2014 12:45:26 GMT

around 21 Tags and each tag having minimum 10 and maximum 25 to 30 Words.

Re: Scripting Help

vanderson009 — Mon, 07 Jul 2014 12:48:28 GMT

Hi Malek,

How could i apply vLookup function in qlikview.

can we apply vLookup even both table doesnt having link between them.

- Regards,

Villyee

Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 12:53:59 GMT

That's because you're trying to set a variable name with a space in it.. You could use replace to remove them although I would leave that as it was for wildmatch and that won't work as far as I can see.

Richard

Re: Re: Scripting Help

maleksafa — Mon, 07 Jul 2014 13:01:03 GMT

Attached is a sample for the lookup function, you should try it. so first split your tags and then apply the lookup function.

Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 13:06:05 GMT

and here's the code to split your words....

TableB_Temp1:

Load * Inline [
Tags, Words
CE negative, "frustrating, frustrated, frustration"
CE positive, "efficient, prompt, satisfied"
Staff poor, "friendlier, rude, not pleasant"
];

TableB_Temp2:
Load
Tags
,trim(SubField(Words,',',1)) as Word_01
,trim(SubField(Words,',',2)) as Word_02
,trim(SubField(Words,',',3)) as Word_03
,trim(SubField(Words,',',4)) as Word_04
// Add additional lines to get you to 20+ words

Resident TableB_Temp1;

TableB:
CrossTable (Words,Value)
Load * Resident TableB_Temp2;

Drop Table TableB_Temp1,TableB_Temp2;
Drop field Words;

Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 13:21:14 GMT

Hi Malek,

I don't think lookup will work in this instance as the word is in a string so it can't wildmatch it.

Richard

Re: Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 13:50:02 GMT

Hi Villyee,

Ok, I've gone back to using substringcount.

the attached QVW will count up all the instances of your key words within the comments... Its a bit of a nasty loop so I'd just load the ID and comments like I have first and then left join your data once its completed.

From the sums you can easily add the flag with a conditional statement.

Unforntunaly its not fully dynamic. If more tags are added (more words are fine) you will need to update the code.

Regards

Richard

Re: Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 13:51:39 GMT

Hi,

Managed to find a solution. I've uploaded the QVW. just waiting for it to show on here.

Richard

Re: Re: Re: Scripting Help

richard_pearce6 — Mon, 07 Jul 2014 13:58:35 GMT

QVW

QlikCentral.Com

Re: Scripting Help

simondachstr — Mon, 07 Jul 2014 20:44:33 GMT

Hi Villy,

try below.

Regards,

Re: Re: Re: Scripting Help

richard_pearce6 — Tue, 08 Jul 2014 07:53:12 GMT

Morning Villyee,

Did the QVW I posted resolve your question? If so can you mark the reply as ansered to help other people find help when they have a similar question.

Thanks

Richard

Re: Scripting Help

vanderson009 — Tue, 08 Jul 2014 08:34:25 GMT

Morning to you Richard,

Appriciate your efforts, but yet i am not achieve my task.

- Thanks,

Villyee Anderson

Re: Scripting Help

richard_pearce6 — Tue, 08 Jul 2014 09:28:45 GMT

Morning Villyee,

Which aspect of the QVW I posted did not meet your needs? Maybe I can help

Richard