Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Attached is a sample for the lookup function, you should try it. so first split your tags and then apply the lookup function.
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;
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
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
Hi,
Managed to find a solution. I've uploaded the QVW. just waiting for it to show on here.
Richard
QVW
QlikCentral.Com
Hi Villy,
try below.
Regards,
MM
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
Morning to you Richard,
Appriciate your efforts, but yet i am not achieve my task.
- Thanks,
Villyee Anderson
Morning Villyee,
Which aspect of the QVW I posted did not meet your needs? Maybe I can help
Richard