Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vanderson009
Creator III
Creator III

Scripting Help

Hello guys,

I have 2 tables like below.

TableA :

Column1Column2Comment

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:

TagsWords
CE negativefrustrating, frustrated, frustration
CE positiveefficient, prompt, satisfied
Staff poor

friendlier, rude, not pleasant

Please help me for this task....

-- Thanks and Regards,

Villyee Anderson

23 Replies
maleksafa
Specialist
Specialist

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni


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
Luminary Alumni
Luminary Alumni


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
Luminary Alumni
Luminary Alumni

Hi,

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

Richard

richard_pearce6
Luminary Alumni
Luminary Alumni

QVW

QlikCentral.Com

simondachstr
Luminary Alumni
Luminary Alumni

Hi Villy,

try below.

Regards,

MM

richard_pearce6
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III
Author

Morning to you Richard,

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

- Thanks,

Villyee Anderson

richard_pearce6
Luminary Alumni
Luminary Alumni

Morning Villyee,

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

Richard