Keyword Mapping with Description

[Anonymous]

Hi Friends,

Keyword and Descriptions are in different tables as below

 

I am expecting the output like below: -

Can anyone help me.

[settu_periasamy]

Yes you are correct.

You can try the below script

Table1:
LOAD RowNo()&Keyword as Keyword_,
  RowNo()&Issue as Issue_,
  RowNo()&Automation as Automation_;
LOAD * INLINE [
     Keyword, Issue, Automation
    cpu,  CPU Utilization, yes
    memory,  Memory Utilization, yes
    disk space,  Disk Space/File System, yes
    processor, CPU Utilization, yes
    mount point, Unknown, no
    process killed, Unknown, no
];

Temp_Tab1:
Load Distinct Concat(chr(39)&chr(42)&Keyword_&chr(42)&chr(39),',') as Keyword_New,
  Concat(chr(39)&Issue_&chr(39),',') as Issue_New,
  Concat(chr(39)&Automation_&chr(39),',') as Automation_New Resident Table1;

Let vKeyword=Purgechar(Peek('Keyword_New'),'1234567890');
Let vIssue=Purgechar(Peek('Issue_New'),'1234567890');
Let vAutomation=Purgechar(Peek('Automation_New'),'1234567890');

DROP Tables Temp_Tab1,Table1;

Result:
LOAD TicketID, 
     Description,
     if(WildMatch(Description,$(vKeyword))>0,1,0) as IsMapped,
     Purgechar(Pick(WildMatch(Description,$(vKeyword)),$(vKeyword)),'*') as Keyword,
     Pick(WildMatch(Description,$(vKeyword)),$(vIssue)) as Issue,
     Pick(WildMatch(Description,$(vKeyword)),$(vAutomation)) as Automation 
FROM
[Keyword Mapping.xlsx]
(ooxml, embedded labels, table is Sheet2);

[sasiparupudi1]

Hi

I think your output got messed up because I used @ sign as a delimeter not thinking that email address might be part of your description. I have changed the delimeter to !!!. Hopefully it should show the correct result. Please give the following script a try

Table1:

LOAD * INLINE [

     Keyword, Issue, Automation

    cpu,  CPU Utilization, yes

    memory,  Memory Utilization, yes

    disk space,  Disk Space/File System, yes

    processor,     CPU Utilization,    yes

    mount point,    Unknown,    no

    process killed,    Unknown,    no

];

Map:

mapping LOAD Keyword, '!!!'&Issue&'_'&Automation&'!!!'

Resident Table1;

drop Table Table1;

Table2:

LOAD TicketID,

     Description,

     if(MapSubString('Map',lower(Description))<> lower(Description),SubField(TextBetween(MapSubString('Map',lower(Description)),'!!!','!!!'),'_',1))as keyword,

          if(MapSubString('Map',lower(Description))<> lower(Description),SubField(TextBetween(MapSubString('Map',lower(Description)),'!!!','!!!'),'_',2))as Automation

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

thx

Sasi

[MarcoWedel]

Hi,

one solution could be also:

tabKeywords:
LOAD Keyword,
     Issue,
     Automation
FROM [https://community.qlik.com/servlet/JiveServlet/download/854119-182345/Keyword%20Mapping.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))))
Where Len(Keyword);

mapKeywords:
Mapping LOAD Keyword,
             '@start@'&Keyword&'@end@'
Resident tabKeywords;

tabTickets:
LOAD TicketID,
     Description
FROM [https://community.qlik.com/servlet/JiveServlet/download/854119-182345/Keyword%20Mapping.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))));

tabLink:
LOAD TicketID,
     TextBetween(temp,'@start@','@end@',IterNo()) as Keyword
While IterNo()<=SubStringCount(temp,'@start@');
LOAD TicketID,
     MapSubString('mapKeywords',Lower(Description)) as temp
Resident tabTickets;

hope this helps

regards

Marco

[MarcoWedel]

maybe helpful:

Multi-wildmatch on Text

regards

Marco

[ziadm · Accepted solution]

HI

This is the final result

attached is the QVW file

Keywords:

LOAD RowNo() AS KeyID ,* INLINE [

Keys

CPU

MEMORY

DISK SPACE

];

TICKETSFILE:

LOAD TicketID,

     Description

FROM

(ooxml, embedded labels, table is Sheet2);

ISSUES_MAP:

MAPPING LOAD

  Keys,

  '§' & RowNo() & '¨' AS Symbol

RESIDENT

  Keywords;

TICKETS:

LOAD TicketID,

     Description,

     MapSubString( 'ISSUES_MAP', Description ) AS KeywordText,

     SubStringCount( MapSubString( 'ISSUES_MAP', Description ) , '§' ) AS NoKeywords,

     TextBetween( MapSubString( 'ISSUES_MAP' , Description ) , '§' , '¨' , IterNo() ) AS KeyID

     Resident

     TICKETSFILE

WHILE

  IterNo() <= SubStringCount( MapSubString( 'ISSUES_MAP', Description ) , '§' );

DROP Table TICKETSFILE;