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: 
Anonymous
Not applicable

Keyword Mapping with Description

Hi Friends,

Keyword and Descriptions are in different tables as below

 

I am expecting the output like below: -

Can anyone help me.

1 Solution

Accepted Solutions
ziadm
Specialist
Specialist

HI

This is the final result

attached is the QVW file

Tickets.png

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;

View solution in original post

14 Replies
sasiparupudi1
Master III
Master III

Provide sample data please

Anonymous
Not applicable
Author

Thank you.

Please find the attachment of the sample data.

settu_periasamy
Master III
Master III

Hi,

Check this script & attachment.

Table1:

LOAD * INLINE [

     Keyword, Issue, Automation

    cpu,  CPU Utilization, yes

    memory,  Memory Utilization, yes

    disk space,  Disk Space/File System, yes

];

Temp_Tab1:

Load Concat(chr(39)&chr(42)&Keyword&chr(42)&chr(39),',') as Keyword_New,

  Concat(chr(39)&Issue&chr(39),',') as Issue_New Resident Table1;


Let vKeyword=Peek('Keyword_New');

Let vIssue=Peek('Issue_New');


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    

FROM

[QVcomm\Keyword Mapping.xlsx]

(ooxml, embedded labels, table is Sheet2);

Anonymous
Not applicable
Author

Thanks Settu.

But when i added some more data in the inline table, the keywords are not mapping properly to the Issue.

please find the code below.

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

];

Temp_Tab1:

Load 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=Peek('Keyword_New');

Let vIssue=Peek('Issue_New');

Let vAutomation=Peek('Automation_New');

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

     Resident Incident;   

settu_periasamy
Master III
Master III

Hi,

is it possible to post your file which is not woking?

Anonymous
Not applicable
Author

Hi Settu, Please find the attachment.

sasiparupudi1
Master III
Master III

Please 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);

Anonymous
Not applicable
Author

No It is not working. Keywords and Automation are Wrong.

Anonymous
Not applicable
Author

Hi Settu,

while concatenating it is getting sorted by default to ascending order.

we need to Change it. if it done, then hope it should come correctly.

Thank you