Scripting Help

[vanderson009]

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

[maleksafa]

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

[richard_pearce6]

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;

[richard_pearce6]

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

[richard_pearce6]

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

[richard_pearce6]

Hi,

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

Richard

[richard_pearce6]

QVW

QlikCentral.Com

[simondachstr]

Hi Villy,

try below.

Regards,

MM

[richard_pearce6]

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

[vanderson009]

Morning to you Richard,

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

- Thanks,

Villyee Anderson

[richard_pearce6]

Morning Villyee,

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

Richard